How to Rename a Sheet Using Excel VBA (7 Examples)

It’s quite common to need to rename an Excel sheet. A sheet can be renamed effortlessly by double-clicking on the sheet name and typing the new name. However, if you need to accomplish a specific task, Excel VBA might be able to help. We will show you 7 different examples to rename sheet using Excel VBA in this article.

Example #1 Rename an Active Worksheet

To rename the current name of the active worksheet, copy the following code and run it.

Sub Rename_Active_Sheet()
ActiveSheet.Name = "New Sheet"
End Sub

In the image below, see that the previous name is changed to our assigned name, “New Sheet”

Example #2 Rename a Specific Worksheet

While working on a different worksheet, you can change the name of a specific worksheet by using the codes below:

Sub Rename_Specific_Sheet()
Sheets("Old WorkSheet").Name = "New WorkSheet"
End Sub

In the image below, you can see that the previous name was “Old WorkSheet” which has been changed to the current name “New WorkSheet”.

Example #3 Renaming Sheets by Index Number

You can rename a sheet by its index position number. If you want to change the name of the third sheet, your index number is 3. To do this, use the codes below: 

Sub Rename_Sheet_by_Index()
'Sheet(3) refers to the third sheet from the Workbook
Sheets(3).Name = "Rename Sheet by Index"
End Sub

By applying the above codes, we have changed the name of the third sheet to “Rename Sheet by Index.”

Example #4 Rename Sheet Dynamically by User Input

It is probably one of the most useful and dynamic ways to change the name of worksheets. You can just type the name of the old worksheet and your preferred name to rename it. Use the following codes below to see the results.

Sub Rename_Sheet_User_Input()

Old_Worksheet_Name = InputBox("Type the Name of the Old Worksheet: ")
New_Worksheet_Name = InputBox("Type the New Name of the Worksheet to Rename: ")
Sheets(Old_Worksheet_Name).Name = New_Worksheet_Name

End Sub

Type the old worksheet name in the first InputBox. 

Type your preferred name to rename the worksheet name.

Thus, you can see your worksheet is renamed to “Renamed by User” from “Sheet3”.

Example #5 Rename Sheet Based on Cell Value

Additionally, you can also rename a sheet based on its cell value. Let’s say we have a value in cell A1, and we want to assign the same name as the worksheet name. Use the codes below to do the task.

Sub Rename_Based_on_Cell_Value()

Dim wSheet As Worksheet
Set wSheet = ActiveSheet
   
wSheet.Name = Range("A1").Value

End Sub

You can see in the image below that our cell value in A1 is “Rename Based on Cell Value”. After running the program, our worksheet name is also changed according to the cell value.

Example #6 Rename Multiple Worksheets Based on Cell Range

In the previous example, we renamed a sheet based on just one particular sheet. If you want to rename multiple worksheets at once based on cell range values, just use the codes below.

Sub Rename_multiple_ws()
   
    Dim wSheet As Worksheet
    Dim p As Long
     
    p = 1 ' Initialize p to 1
   
    For Each wSheet In ThisWorkbook.Worksheets
        wSheet.Name = Range("A1:A5").Cells(p, 1).Value
        p = p + 1 ' Increment p for the next worksheet
    Next wSheet
   
    Exit Sub

End Sub

After running the codes, you can see that our cell range A1:A5 has five different values, and the worksheets are changed according to the cell values in sequential order.

Example #7 Display All Worksheet Names in a Single Worksheet

Now, if we want to display our all worksheet names in a single sheet, you can go over this example. We want to display all the sheet names in my worksheet “All WorkSheets Name”. Simply, use the codes below:

Sub All_WorkSheets_Name()

Dim wSheet As Worksheet
  Dim row_num As Long

  For Each wSheet In ActiveWorkbook.Worksheets
  row_num = Worksheets("All WorkSheets Name").Cells(Rows.Count, 1).End(xlUp).Row + 1
  Cells(row_num, 1).Select
  ActiveCell.Value = wSheet.name
  Next wSheet

End Sub

As a result, you can see in the image below, that all of the existing sheet names are displayed in my current worksheet. 

Conclusion

From the above discussion, you can now rename a sheet using Excel VBA. If you have any additional questions, kindly leave a comment for us. In response, you will provide solutions from the Solved Excel research wing. Stay safe and get connected with us!