May 27, 2017/ Chris Newman
Show
Referencing Worksheets in ExcelWhen you are working with VBA inside Excel, it is more than likely that you will be automating some sort of changes to a worksheet inside your file. The following VBA guide is intended to show you have your can target specific worksheets within your workbooks in order to apply changes to them. Reference Worksheet By Code Name [BEST PRACTICE!]Sheet1.Range("A1").Value = 100 Reference Worksheet By NameThisWorkbook.Worksheets("Summary Tab").Range("A1").Value = 100 Reference Currently Viewed WorksheetActiveSheet.Range("A1").Value = 100 Reference Worksheet By PositionThisWorkbook.Worksheets(3).Range("A1").Value = 100 Reference Last Worksheet In WorkbookThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Range("A1").Value = 100 Reference Worksheet Within Another WorkbookWorkbooks("Book2").Worksheets("Sheet1").Range("A1").Value = 100 Store Worksheet To A VariableDim sht As Worksheet Set sht = ThisWorkbook.Worksheets("Summary Tab") Store Newly Created Worksheet To A VariableDim sht As Worksheet Set sht = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets.Count) Loop Through Every Worksheet In A WorkbookSub WorksheetLoop() Dim sht As Worksheet For Each sht In ThisWorkbook.Worksheets sht.Range("A1").Value = 100 Next sht End Sub Loop Through Every Worksheet In Reverse OrderSub WorksheetReverseLoop() Dim x As Long 'Reverse Loop Through Sheets End Sub Loop Through A List Of Worksheet NamesSub WorksheetListLoop() Dim SheetList As Variant 'List Sheet Names into an Array Variable 'Loop through list End Sub About The AuthorHey there! I’m Chris and I run TheSpreadsheetGuru website in my spare time. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company. Through my years in the corporate world, I’ve been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters. I hope I was able to provide you with some value today and I hope to see you back here soon! - Chris
Worksheets(“Sales”) After mentioning the sheet name, we need to select the “Name” property to change the worksheet name. Worksheets(“Sales”).Name Now, we need to set the name property to the name as per our wish. For example, assume you want to change the “Sales” to “Sales Sheet,” then put an equal sign after the “NAME” property and enter the new name in double quotes. Worksheets(“Sales”).Name = “Sales Sheet” Like this, we can change the worksheet name using the Name property. Examples to Name Worksheet using VBAExample #1Change or Rename Sheet using Variables. Look at the below sample code. Code: Sub Name_Example1() Dim Ws As Worksheet Set Ws = Worksheets("Sales") Ws.Name = "Sales Sheet" End Sub First, we have declared the variable as “Worksheet.” Dim Ws As Worksheet Next, we have set the reference to the variable as “Sales” using the worksheet object. Set Ws = Worksheets("Sales") Now, the variable “Ws” holds the reference of the worksheet “Sales.” Now, using the “Ws” variable, we have renamed the worksheet “Sales Sheet.” This code will change the “Sales” name to “Sales Sheet.” Important Note to Remember We just have seen how to change the name of the Excel worksheet from one name to another. However, if we run the code again, we will get a Subscript Out of Range errorSubscript out of range is an error in VBA that occurs when we attempt to reference something or a variable that does not exist in the code. For example, if we do not have a variable named x but use the msgbox function on x, we will receive a subscript out of range error.read more. One of the keys to getting an expert in VBA MacrosVBA Macros are the lines of code that instruct the excel to do specific tasks, i.e., once the code is written in Visual Basic Editor (VBE), the user can quickly execute the same task at any time in the workbook. It thus eliminates the repetitive, monotonous tasks and automates the process.read more is to handle errors. However, before handling errors, we need to know why we are getting this error. We get this error because, in the previous step itself, we have already changed the worksheet named “Sales” to “Sales Sheet.” We do not have any ” Sales ” sheet; we will get this subscript out of range error. Example #2Get all the worksheet names in a single sheet. Assume you have plenty of worksheets in your workbook. You want to get the name of all these worksheets in any single worksheet. We can do this by using VBA codingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. For example, look at the below image. We have so many sheets here. Of all these sheets, we need the name of each sheet in the sheet called “Index Sheet.” Therefore, we have written the below code for you. Code: Sub All_Sheet_Names() Dim Ws As Worksheet Dim LR As Long For Each Ws In ActiveWorkbook.Worksheets LR = Worksheets("Index Sheet").Cells(Rows.Count, 1).End(xlUp).Row + 1 'This LR varaible to find the last used row Cells(LR, 1).Select ActiveCell.Value = Ws.Name Next Ws End Sub Now, copy this code to your module. Now, run the code by naming any worksheets “Index Sheet.” This code will give all the worksheet names in the “Index Sheet.” Like this, using the “NAME” property of the worksheet in VBAExcel is a workbook, and worksheets or sheets are included within that workbook. Sheets are what we call them in a regular Excel file, but they're called "Worksheets" in VBA. The term "Worksheets" refers to all of a worksheet's collections.read more, we can play around with the name of the worksheets. For example, we can rename, extract, and choose the specific worksheet and do many other things that we can do by using the “Name” property. Things to Remember
Recommended ArticlesThis article is a guide to the VBA Name Worksheet. Here, we discuss naming worksheets using VBA coding, practical examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –
|