How to Rename Sheet with VBA in Excel ( 2 Suitable Ways)

In this article, I’ll show you how you can rename the sheet with VBA in Excel. You’ll learn to rename both a single sheet and a group of sheets with proper examples and illustrations. Look at the overview of this article before diving into the methods.

Rename Sheet with VBA in Excel (Quick View)

Sub Rename_Sheet()
Old_Name = InputBox("Name of the Worksheet That You Want to Change: ")
New_Name = InputBox("New Name of the Worksheet: ")
Sheets(Old_Name).Name = New_Name
End Sub

How to Rename Sheet with VBA in Excel: 2 Suitable Ways

Here we’ve three worksheets with the Sales Record of three months of a company called Mars Group.

The worksheets are named Sheet1, Sheet2, and Sheet3 respectively.

Today our objective is to rename the sheets with Visual Basic Application (VBA).

Read More: How to Select Sheet by Variable Name with VBA in Excel

1. Rename a Single Sheet with VBA in Excel

Let’s try to rename a single sheet first. Renaming a single sheet with VBA is easy. You can use the following code to accomplish this.

⧭ VBA Code:

Sub Rename_Sheet()
Old_Name = InputBox("Name of the Worksheet That You Want to Change: ")
New_Name = InputBox("New Name of the Worksheet: ")
Sheets(Old_Name).Name = New_Name
End Sub

⧪ Note: This code creates a Macro called Rename_Sheet.

⧭ Step by Step Procedure to Run the Macro:

⧪ Step 1: Opening the VBA Window

➤ Press ALT+F11 on your keyboard. The VBA window will open.

⧪ Step 2: Inserting a New Module

➤ Go to the Insert tab in the VBA window. 

➤ From the options available, select Module.

⧪ Step 3: Entering the VBA Code

➤ A new module window called Module 1 will open. 

➤ Insert the given VBA code in the opened Module.

⧪ Explanation of the Code:

• Sub Rename_Sheet() starts a new macro called Rename_Sheet.
• Old_Name = InputBox(“Name of the Worksheet That You Want to Change: “) asks the user to input the name of the worksheet that he/she wants to change.
• New_Name = InputBox(“New Name of the Worksheet: “) asks the user to enter the new name of the worksheet.
• Sheets(Old_Name).Name = New_Name changes the old name of the worksheet to the new name. This is the most important line.
• End Sub declares the end of the macro.

⧪ Step 4: Saving the Macro-Enabled Workbook

➤ Save the workbook in the Excel Macro-Enabled Workbook format.

⧪ Step 5: Running the Macro

➤ Then come back to any of your worksheets and press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Rename_Sheet (Name of the Macro) and click on Run.

⧪ Step 6: Entering the Inputs 

➤  You will get two Input Boxes. The 1st box will ask you to enter the name of the old sheet that you want to change. Let us input Sheet1.

➤ Click OK. The 2nd box will ask you to enter the new name of the worksheet. Let us input January.

⧪ Step 7: The Final Output!

➤ Then click OK.

➤ You will find the name of your selected sheet changed beautifully to the desired name (Sheet1 to January in this example).

⧭ Things to Remember:

• The punch line of the code is the line Sheets(Old_Name).Name = New_Name.
• It changes any worksheet with the name kept in the variable Old_Name to a new name kept in the variable New_Name.
• Instead of taking the help of the variables, you can directly put the names of the worksheets in the code.
• For example, Sheets(“Sheet1″).Name=”January” would have also worked.
• But that would have reduced the flexibility of the code. Each time you want to change a different sheet, you have to go and change the name inside the code.
• That’s why we left it in the hands of the users, with the help of two input boxes. Now you can change any worksheet according to your wish at any time.

2. Rename Multiple Sheets with VBA in Excel

In the previous section, we derived a macro to rename the name of a single sheet of a workbook.

Now we’ll develop a macro to rename multiple sheets with VBA in a workbook.

You can use the following VBA code for this purpose.

⧭ VBA Code:

Sub Rename_Multiple_Sheets()

Alphabets = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")

Days = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")

Dim Weekdays(5) As String

For i = 0 To 4
    Weekdays(i) = Days(i)
Next i

Months = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

Old_Names = InputBox("Enter the Names of the Worksheets to Change (Separate them be Commas)." + vbNewLine + "OR" + vbNewLine + "Enter ALL to change all the worksheets.")

If Old_Names = "ALL" Then
    Dim Old_Sheets() As String
    ReDim Old_Sheets(Sheets.Count - 1)
    For i = 0 To Sheets.Count - 1
        Old_Sheets(i) = Sheets(i + 1).Name
    Next i
Else
    Old_Sheets = Split(Old_Names, ",")
End If

Dim Used_Names() As String

ReDim Used_Names(0)

Dim Sign As Integer

Sequential_Or_Random = Int(InputBox("Enter 1 to Change the Worksheet Names in a Sequential Way: " + vbNewLine + "OR" + vbNewLine + "Enter 2 to Change the Worksheet Names in a Random Way: "))

If Sequential_Or_Random = 1 Then

    Series = Int(InputBox("Enter 1 to Change the Names to a Series of Numbers: " + vbNewLine + "Enter 2 to Change the Names to a Series of ALphabets: " + vbNewLine + "Enter 3 to Change the Names to a Series of Days: " + vbNewLine + "Enter 4 to Change the Names to a Series of Weekdays: " + vbNewLine + "Enter 5 to Change the Names to a Series of Months: "))

    If Series = 1 Then
        Prefix = InputBox("Enter the Prefix before the Numbers: ")
        First_Number = Int(InputBox("Enter the First Number: "))
        Increment = Int(InputBox("Enter the Increment: "))
        For i = 0 To UBound(Old_Sheets)
            Sheets(Old_Sheets(i)).Name = Prefix + Str(First_Number + Increment * (i))
        Next i

    ElseIf Series = 2 Then
        Prefix = InputBox("Enter the Prefix before the Letters: ")
        First_Letter = InputBox("Enter the First Letter: : ")
        Increment = Int(InputBox("Enter the Increment: "))
        Dim Case_Identifier As String
        For i = 0 To UBound(Alphabets)
            If Alphabets(i) = First_Letter Then
                First_Letter_Number = i
                Case_Identifier = "U"
                Exit For
            ElseIf LCase(Alphabets(i)) = First_Letter Then
                First_Letter_Number = i
                Case_Identifier = "L"
                Exit For
            End If
        Next i
        For i = 0 To UBound(Old_Sheets)
            Sign = 0
            For j = 0 To UBound(Used_Names)
                If Alphabets((First_Letter_Number + (Increment * i)) Mod 26) = Used_Names(j) Then
                    Sign = Sign + 1
                End If
            Next j
            If Sign = 0 Then
                If Case_Identifier = "U" Then
                    Sheets(Old_Sheets(i)).Name = Prefix + Alphabets((First_Letter_Number + (Increment * i)) Mod 26)
                Else
                    Sheets(Old_Sheets(i)).Name = Prefix + LCase(Alphabets((First_Letter_Number + (Increment * i)) Mod 26))
                End If
            Else
                If Case_Identifier = "U" Then
                    Sheets(Old_Sheets(i)).Name = Prefix + Alphabets((First_Letter_Number + (Increment * i)) Mod 26) + " (" + Str(Sign) + ")"
                Else
                    Sheets(Old_Sheets(i)).Name = Prefix + LCase(Alphabets((First_Letter_Number + (Increment * i)) Mod 26)) + " (" + Str(Sign) + ")"
                End If
            End If
            ReDim Preserve Used_Names(i)
            Used_Names(i) = Alphabets((First_Letter_Number + (Increment * i)) Mod 26)
        Next I

    ElseIf Series = 3 Then
        First_Day = LCase(InputBox("Enter the First Day: : "))
        Increment = Int(InputBox("Enter the Increment: "))
        For i = 0 To UBound(Days)
            If LCase(Days(i)) = First_Day Then
                First_Day_Number = i
                Exit For
            End If
        Next i
        For i = 0 To UBound(Old_Sheets)
            Sign = 0
            For j = 0 To UBound(Used_Names)
                If Days((First_Day_Number + (Increment * i)) Mod 7) = Used_Names(j) Then
                    Sign = Sign + 1
                End If
            Next j
            If Sign = 0 Then
                Sheets(Old_Sheets(i)).Name = Days((First_Day_Number + (Increment * i)) Mod 7)
            Else
                Sheets(Old_Sheets(i)).Name = Days((First_Day_Number + (Increment * i)) Mod 7) + " (" + Str(Sign) + ")"
            End If
            ReDim Preserve Used_Names(i)
            Used_Names(i) = Days((First_Day_Number + (Increment * i)) Mod 7)
        Next i

    ElseIf Series = 4 Then
        First_Weekday = LCase(InputBox("Enter the First Day: : "))
        Increment = Int(InputBox("Enter the Increment: "))
        For i = 0 To UBound(Weekdays)
            If LCase(Weekdays(i)) = First_Weekday Then
                First_Weekday_Number = i
                Exit For
            End If
        Next i
        For i = 0 To UBound(Old_Sheets)
            Sign = 0
            For j = 0 To UBound(Used_Names)
                If Weekdays((First_Weekday_Number + (Increment * i)) Mod 5) = Used_Names(j) Then
                    Sign = Sign + 1
                End If
            Next j
            If Sign = 0 Then
                Sheets(Old_Sheets(i)).Name = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5)
            Else
                Sheets(Old_Sheets(i)).Name = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5) + " (" + Str(Sign) + ")"
            End If
            ReDim Preserve Used_Names(i)
            Used_Names(i) = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5)
        Next i

    ElseIf Series = 5 Then
        First_Month = LCase(InputBox("Enter the First Month: "))
        Increment = Int(InputBox("Enter the Increment: "))
        For i = 0 To UBound(Months)
            If LCase(Months(i)) = First_Month Then
                First_Month_Number = i
                Exit For
            End If
        Next i
        For i = 0 To UBound(Old_Sheets)
            Sign = 0
            For j = 0 To UBound(Used_Names)
                If Months((First_Month_Number + (Increment * i)) Mod 12) = Used_Names(j) Then
                    Sign = Sign + 1
                End If
            Next j
            If Sign = 0 Then
                Sheets(Old_Sheets(i)).Name = Months((First_Month_Number + (Increment * i)) Mod 12)
            Else
                Sheets(Old_Sheets(i)).Name = Months((First_Month_Number + (Increment * i)) Mod 12) + " (" + Str(Sign) + ")"
            End If
            ReDim Preserve Used_Names(i)
            Used_Names(i) = Months((First_Month_Number + (Increment * i)) Mod 12)
        Next i
    End If

ElseIf Sequential_Or_Random = 2 Then
    New_Names = InputBox("Enter the New Names (Separate them by Commas): ")
    New_Sheets = Split(New_Names, ",")
    For i = 0 To UBound(Old_Sheets)
        Sign = 0
        For j = 0 To UBound(Used_Names)
            If New_Sheets(i) = Used_Names(j) Then
                Sign = Sign + 1
            End If
        Next j
        If Sign = 0 Then
            Sheets(Old_Sheets(i)).Name = New_Sheets(i)
            Used_Names(Count) = New_Sheets(i)
            Count = Count + 1
        Else
            Sheets(Old_Sheets(i)).Name = New_Sheets(i) + " (" + Str(Sign) + ")"
        End If
        ReDim Preserve Used_Names(i + 1)
        Used_Names(i + 1) = New_Sheets(i)
    Next i
End If

End Sub

⧪ Note: This code creates a macro called Rename_Multiple_Sheets.

⧭ Step by Step procedure to Run the Macro:

⧪ Step 1: The Initial Steps

➤ Run steps 1-4 from the previous method to open the VBA window, insert a new module, enter the VBA code, and then finally save the Macro-Enabled Workbook.

⧪ Step 2: Running the Macro

➤ Come back to any worksheet and press ALT+F8 on your keyboard.

➤ From the Macros available, select Rename_Multiple_Sheets and click on Run.

⧪ Step 3: Entering the Inputs

➤ You will get a few input boxes. The first one will ask you to enter the name of the sheets to change.

Enter the name of the sheets that you want to change. Separate them by commas (,).

For example, to change Sheet1 and Sheet3, enter Sheet1, Sheet3 (No space after the commas).

Or to change all the worksheets, enter ALL.

Here I want to change the names of all the worksheets. So I have entered ALL.

➤ The 2nd input box will ask you whether you change the sheet names in a sequential way or in a random way.

In a sequential way, you can change them to a series of days like Monday, Tuesday, Wednesday, etc.

Or in a series of Months like January, February, March, etc.

Or in a series of Numbers like 10, 11, 12, etc.

Also in a series of numbers associated with a Prefix like Day1, Day2, Day3, etc.

Or in a series of Alphabets like A, B, C, etc.

Also in a series of alphabets associated by a Prefix like ProductA, ProductB, ProductC, etc.

In a random way, you can change the names to any random values according to your desire.

Enter 1 for a sequential way or 2 for a random way.

I want them to be renamed in a sequential way, so I have entered 1.

➤ If you go for a sequential way, the 3rd input box will ask for a series of values from a few series.

Enter 1 for a series of Numbers (1, 2, 3, etc.)

Or 2 for a series of Alphabets (A, B, C, etc.)

Or 3 for a series of Days (Sunday, Monday, Tuesday, etc.)

Or 4 for a series of Weekdays (Friday, Monday, Tuesday, etc.)

Or 5 for a series of Months (January, February, March, etc.)

I want a series of Months. So I have entered 5.

⧪ Extra: If you had selected a random way, this input box would have asked you to enter the random new names.

You had to enter them separated by commas.

For example, Washington,New York,Los Angels (No space after the commas.)

➤ The next input box will ask you the first value by which you want to start.

If you choose Numbers, it will ask you the first number.

For Alphabets, it will ask you for the first letter.

If you choose Days, it will ask you the first day to rename.

For Weekdays, it will ask you the first weekday.

And for Months, it will ask you the first month.

I have selected Months, so it’s asking me the first month to start with.

I want to start with January. So, I have entered January.

You enter it according to your needs.

⧪ Extra: When you selected a series of Numbers, you would have got one more input box in between that would have asked you the Prefix.

A Prefix is something you want to add before the numbers.

For example, if you want to rename the sheets in Product1, Product2, Product3 in this way, the prefix is Product.

And if you want no prefix, just a series of numbers like 1, 2, 3, etc. keep the box empty.

➤ The final input box will ask for the increment.

An increment is something by which each value increases in a series.

For example, in a series of 10, 13, 16, etc. the increment is 3.

In a series of April, June, August, October, etc., the increment is 2.

Here I want a series of January, February, March. So my increment is 1.

You enter your one.

⧪ Step 4: The Final Output!

➤ Then click OK.

➤ You will find the name of your selected sheets changed beautifully according to your desired names (January, February, March in this example).

⧭ Things to Remember: 

• Using this code, you can rename any number of worksheets in your workbook to your desired names (Random names or following a series).
• For example, here I have changed 7 worksheets to the names Item2, Item 4, Item6, Item8, Item10, Item12, and Item14.

Download Practice Workbook

Conclusion

Using these methods, you can rename a single sheet or a number of sheets in your workbook with VBA quite handsomely. Do you have any questions? Feel free to ask us on our website.

Related Readings

• How to Search Sheet Name with VBA in Excel