VBA Name Worksheet

In VBA, to name a worksheet does not need any special skills. However, we need to reference which sheet name we are changing by entering the current sheet name. For example, if we want to change the "Sales" sheet, we need to call the sheet by its name using the 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. 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.

Example #1

Change 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 error.

One of the keys to getting an expert in VBA Macros 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 #2

Get 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 coding.

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 VBA, 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.

• The NAME in VBA is property.
• Using this name, we can rename the worksheet, and also we can extract sheet names.
• We can change any worksheet name in the specified workbook if you refer to other workbooks than the code-written workbook.
• If the worksheet name does not match, we will get “Subscript out of range.”