Excel VBA Name WorkSheet
In VBA, to name a worksheet doesn’t need any special skills. We just need to reference which sheet name we are changing by entering the existing sheet name. For example, if we want to change the sheet named “Sales” then we need to call the sheet by its name using Worksheet object.
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. Assume you want to change the “Sales” to “Sales Sheet,” then put equal sign after “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 VBA
Example #1
Change or Rename Sheet using Variables.
For example, look at the below sample code.
Code:
Sub Name_Example1() Dim Ws As Worksheet Set Ws = Worksheets("Sales") Ws.Name = "Sales Sheet" End Sub
In the above first, I have declared the variable as Worksheet.
Dim Ws As Worksheet
Next, I have set the reference to the variable as “Sales” using worksheets object.
Set Ws = Worksheets("Sales")
Now the variable “Ws” holds the reference of the worksheet “Sales.”
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Now using the “Ws” variable, I have renamed the worksheet as “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 error.
One of the keys to getting an expert in VBA Macros is to handle errors. Before handling errors, we need to know why we are getting this error.
The reason why we get this error because, in the previous step itself, we have already changed the worksheet named “Sales” to “Sales Sheet.”
We don’t have any sheet named as “Sales” we will get this subscript out of range error.
Example #2
Get all the worksheet names in Single Sheet.
Assume you have plenty of worksheets in your workbook, and you want to get the name of all these worksheets in any of the single worksheets. This can be done by using VBA coding.
For example, look at the below image.
We have so many sheets here.
Off all these sheets, we need the name of each sheet in the sheet called “Index Sheet.” I 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 of the worksheets as “Index Sheet.” This code will give all the worksheet names in “Index Sheet.”
Like this, using the “NAME” property of the worksheet in VBA, we can play around with the name of the worksheets. We can rename, extract, and we can choose the specific worksheet and do many other things that can be done by using the “Name” property.
Things to Remember
- NAME in VBA is property.
- Using this name, we can rename the worksheet, and also we can extract sheet names.
- We can change any name of the worksheet in the specified workbook if you are referring to other workbooks than the code written workbook.
- If the worksheet name doesn’t match, we will get subscript out of range.
Recommended Articles
This has been a guide to VBA Name WorkSheet. Here we discuss how to name worksheet using VBA coding along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –
- Create a Reference Object using CreateObject
- Activate Sheet in VBA
- Rename Sheet in VBA
- Editor in VBA
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
