VBA Rename Sheet

Renaming sheets in excel are done from the taskbar below the worksheets are present by double-clicking on them, but in VBA we use Sheets or Worksheet property method to rename the sheet, the syntax to rename a sheet in VBA is as follows Sheets(“ Old Sheet Name”).Name = “New Sheet name”.

Rename Sheet in Excel VBA

We all have done this task of renaming the worksheet as per our identity or as per our convenient isn’t it? Renaming is not the rocket science to master but if you are the VBA coder then you must be knowing this task of renaming the worksheet. Since we work with worksheets by using their names it is important to know the importance of the worksheet names in VBA coding. In this article, we will show you how to rename the sheet using Excel VBA coding.

How to Rename Sheet in VBA?

Changing the name of the 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 rename the sheet named as “Sheet 1” then we need to call the sheet by its name using Worksheet object.

Worksheets(“Sheet1”)

After mentioning the sheet name we need to select the “Name” property to rename the worksheet name.

Worksheets(“Sheet1”).Name

Now we need to set the Name property to the name as per our wish.

Worksheets(“Sheet1”).Name = “New Name”

Like this, we can rename the worksheet name in VBA using Name property.

In the following sections of the article, we will show you more and more examples of changing or renaming the worksheet.

Examples of Rename Worksheets in Excel VBA

Below are the examples of VBA Rename Sheet.

Example #1 – Change or Rename sheet using VBA Variables.

For an example look at the below sample code.

Code:

Sub Rename_Example1()

  Dim Ws As Worksheet
 
  Set Ws = Worksheets("Sheet1")

  Ws.Name = "New Sheet"

End Sub

In the above code first, I have declared the variable as Worksheet.

Dim Ws As Worksheet

Next, I have set the reference to the variable as “Sheet1” using worksheets object.

Set Ws = Worksheets("Sheet1")

Now the variable “Ws” holds the reference of the worksheet “Sheet1”.

Now using “Ws” variable I have renamed the worksheet as “New Sheet”.

This code will change the “Sheet1” name to “New Sheet”.

If I run the code manually or through the shortcut key F5 then, again we will get Subscript Out of Range error.

The reason why we get this error because in the previous step itself we have already changed the worksheet named as “Sheet1” to “New Sheet”. Since there is no longer worksheet name “Sheet1” is not available VBA throws this error.

Example #2 – Get all the Worksheet Names in a Single Sheet.

We can get all the worksheet names of the workbook in a single sheet. Below code will extract all the worksheet names.

Code:

Sub Renmae_Example2()

  Dim Ws As Worksheet
  Dim LR As Long

  For Each Ws In ActiveWorkbook.Worksheets
   LR = Worksheets("Main Sheet").Cells(Rows.Count, 1).End(xlUp).Row + 1
   Cells(LR, 1).Select
   ActiveCell.Value = Ws.Name
  Next Ws

End Sub

This code will extract all the available worksheet names to the sheet named as “Main Sheet”.

Example #3 – Set Permanent Name to the Excel Worksheet Using VBA

Since we work with sheet names in coding it is important to set permanent names to them. How do we set permanent names to them?

For an example look at the below code.

Code:

Sub Rename_Example3()

    Worksheets("Sheet1").Select

End Sub

The above code will select the Sheet1.

If your workbook is used by many people, if someone changed the name of the worksheet then we will get the Subscript Out of Range error.

To avoid this we can set the permanent name to it. To set the permanent name to follow the below steps.

Step 1: Select the sheet we need to set the permanent name to in Visual Basic Editor.

Step 2: Press F4 key to see the Properties window.

Step 3: Under Name, Property Change the name to “New Name”.

As you can see one name is showing as “Sheet1” and in a bracket, we can see the new name as “New Sheet”.

Now in coding, we will use the new name instead of an actual visible name.

Code:

Sub Rename_Example3()

    NewSheet.Select

End Sub

Now come back to worksheet window, we can still see the sheet name as “Sheet1” only.

Now I will change the sheet name to “Sales”.

If I run the code using F5 key or manually then it will still select the sheet named “Sales” only. Since we had given a permanent name to it, still it will select the same sheet only.

Recommended Articles

This has been a guide to VBA Rename Sheet. Here we learn how to Rename Excel Sheet using VBA Code along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• 3 Ways to Comment Block of VBA Code
• VBA ChDir| Examples
• How to Hide Columns in VBA Excel?
• Create Collection Object in VBA
• Excel VBA Text Function
• UsedRange in VBA
• Worksheets in VBA
• Worksheet Function in VBA
• Print in VBA