VBA Rename Sheet

Rename Sheet in Excel VBA

Renaming any excel worksheet is as easy as renaming any file. This can be done by double-clicking on any worksheet name or by selecting Rename from the right list menu list. But what if we want to rename an excel worksheet with the help of VBA macro. With the help of VBA Macro, we can rename one or more than one Excel worksheet in a single shot. We can even fix the name of any worksheet to avoid getting changed by anyone. Here, we will see, multiple ways to rename any worksheet.

How to Rename Sheet in Excel Using VBA?

Below are the different examples to Rename Sheet in Excel using VBA code.

VBA Rename Sheet – Example #1

Although it is very easy to rename any excel worksheet with the help of VBA. There are many ways to do that as well. Here, the name of our worksheet is default set as “Sheet1” as shown below, which we will be renaming.

Step 1: For this example, we would need a module where we will be doing the coding. Go to Insert menu and select Module as shown below.

Step 2: Now in the newly opened Module, write the subcategory in the name of VBA Rename Sheet or in any other name.

Code:

Sub VBA_RenameSheet()

End Sub

Step 3: Now define a variable in any name and assign it with Worksheet datatypes as shown below. Here, we have named it as “Sheet”.

Code:

Sub VBA_RenameSheet()

Dim Sheet As Worksheet

End Sub

Step 4: With the help of Set command, select Sheet variable as shown below.

Code:

Sub VBA_RenameSheet()

Dim Sheet As Worksheet
Set Sheet =

End Sub

Step 5: And select the Worksheet which we need to rename with the help of Worksheet command.

Code:

Sub VBA_RenameSheet()

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

End Sub

Step 6: Now use Sheet function along with Name, and assign the name which we want to rename. Here we are choosing “Renamed Sheet”.

Code:

Sub VBA_RenameSheet()

Dim Sheet As Worksheet
Set Sheet = Worksheets("Sheet1")
Sheet.Name = "Renamed Sheet"

End Sub

Step 7: Now, at last, compile the code and run it if there is an error found. For running the code, click on the Play button located below the menu bar as shown below. We will see, our sheet will get renamed from Sheet1 to Renamed Sheet as shown below.

VBA Rename Sheet – Example #2

In this example, we will see a much easier way to rename any worksheet with the help of VBA macro. For this, we would need, another fresh module.

Step 1: Open a module and write the subcategory of VBA Rename as shown below.

Code:

Sub VBA_RenameSheet1()

End Sub

Step 2: Select the Sheet which we want to rename with Select function. Here our sheet is Sheet1.

Code:

Sub VBA_RenameSheet1()

Sheets("Sheet1").Select

End Sub

Step 3: In the next line, consider the Name function with the selected sheet as shown below.

Code:

Sub VBA_RenameSheet1()

Sheets("Sheet1").Select
Sheets("Sheet1").Name =

End Sub

Step 4: Choose the name which we want to give after renaming the sheet. We are choosing the same name as did in example-1 as “Renamed Sheet”

Sub VBA_RenameSheet1()

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Renamed Sheet"

End Sub

Step 5: Again compile the code and run it. We will see the sheet1 will get Renamed with a new name as “Renamed Sheet”.

VBA Rename Sheet – Example #3

There is again one more similar way to rename an excel worksheet with the help of VBA. This would look a little similar to the process which we have seen in example-2, but much easier to remember and process.

Step 1: Open a module and write the new subcategory of VBA Rename Sheet as shown below.

Code:

Sub VBA_RenameSheet2()

End Sub

Step 2: Select the sheet which we want to rename. Here we can select the sheet by putting the sequence of Sheet into brackets as shown below. So we don’t have to write the name of Sheet to be renamed. Only sequence of that sheet would work.

Code:

Sub VBA_RenameSheet2()

Sheets(1).Select

End Sub

Step 3: In the next line, consider the Name function with the selected sheet sequence as shown below.

Code:

Sub VBA_RenameSheet2()

Sheets(1).Select
Sheets(1).Name =

End Sub

Step 4: Now enter the name which we want to give to selected Sheet1. Let’s consider the name which we have seen in the above examples as “renamed Sheet”.

Code:

Sub VBA_RenameSheet2()

Sheets(1).Select
Sheets(1).Name = "renamed Sheet"

End Sub

Step 5: Now compile the code again and run. We will notice that Sheet1 is now again renamed to “renamed Sheet” as shown below.

VBA Rename Sheet – Example #4

In this example, we will see how to rename any worksheet with just one line of code.

Step 1: Open a module and write the subcategory of VBA Rename Sheet as shown below.

Code:

Sub VBA_RenameSheet3()

End Sub

Step 2: Select the sheet sequence which we want to rename along with Name function and give a name by which we want to rename that sheet as shown below.

Code:

Sub VBA_RenameSheet3()

Sheets(1).Name = "rename Sheet"

End Sub

Step 3: Now simply run the code to implement it. We will again observe that Sheet1 is now renamed as “rename Sheet”

Pros of Excel VBA Rename Sheet

• If we have multiple sheets to be renamed then using VBA code is much better than the manual process.
• We can change the name of any excel worksheet by one line VBA code which we have seen in example-4.
• Although manually changing the name of any sheet is also a simple way of doing it. But VBA Rename Sheet is quite helpful when we are creating a new sheet and we want to separate that with other sheets just to categorize it.

Things to Remember

• To get a better view on changes happening, keep VBA window parallel to excel sheet so that changing to sheet name will be visible clearly.
• Saving the file after work is done in the Macro enable format will allow us to preserve the code for future work as well.
• Automating the process of changing the sheet name will help us in reducing the time categorizing the sheets and data.

Recommended Articles

This is a guide to VBA Rename Sheet. Here we discuss how to Rename Sheet in Excel using VBA code along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Complete Guide to VBA Workbook
2. INDIRECT Function in Excel
3. Tutorials on VBA Count Function
4. How to Use Excel XOR Function?