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 convenience, isn’t it? Renaming is not 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 codingVBA 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. 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 the 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 VBAWorksheet Name In VBATo name a worksheet, simply enter the existing sheet name and refer to which sheet name you're changing. For example, if you want to change the sheet named "Sales," you must use the Worksheet object to call the sheet by its name.read more using the Name property.

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

VBA Rename Sheet

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Rename Sheet (wallstreetmojo.com)

Examples of Rename Worksheets in Excel VBA

Below are the examples of the VBA Rename Sheet.

You can download this VBA Rename Sheet Template here – VBA Rename Sheet Template

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

For 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
VBA Rename Sheet Example 1

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 the “Ws” variable, I have renamed the worksheet as “New Sheet.”

rename example 1

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 a Subscript Out of Range error.

VBA Rename Sheet Example 1-2

The reason why we get this error because, in the previous step itself, we have already changed the worksheet named “Sheet1” to “New Sheet.” Since there is no longer a 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. The 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
VBA Rename Sheet Example 2

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

rename example 2

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 for them?

For example, look at the below code.

Code:

Sub Rename_Example3()

    Worksheets("Sheet1").Select

End Sub
VBA Rename Sheet Example 3

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.

Example 3-1

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

Example 3-2

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

VBA Rename Sheet Example 3-3

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
VBA Rename Sheet Example 3-1

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

Example 3-5

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

Example 3-6

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

Rename Example 3

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 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>