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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel-vba-rename-sheet


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.

Workbook to Rename Sheet with VBA in Excel

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.

Opening the VBA Window to Rename Sheet with VBA in Excel

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.

VBA Code to Rename Sheet with VBA in Excel

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.

Saving Workbook to Rename Sheet with VBA in Excel

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.

Running Macro to Rename Sheet with VBA in Excel

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.

Entering Input to Rename Sheet with VBA in Excel

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.

VBA Code to Rename Sheet with VBA in Excel

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.

Running Macro to Rename Sheet with VBA in Excel

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.

Entering Input to Rename Sheet with VBA in Excel

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.

Entering Input to Rename Sheet with VBA in Excel

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.

Inserting Input to Rename Sheet with VBA in Excel

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.

Embedding Input to Rename Sheet with VBA in Excel

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).

Output to Rename Sheet with VBA in Excel

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.

output


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

3 Comments
  1. Hi, Rifat Hassan. Great effort to write this code to rename multiple sheet at once but this code has debug and not working properly. Kindly check & run this code and modify as required. Thanks

    • Hi Man, Thanks in advance. Can you please tell me where exactly the bug is? I have run the code in every way possible and there evolves no error. I think you couldn’t enter the inputs properly. Please go through the article again and then try. If it still doesn’t work, please tell me what exactly the error is.

  2. Hi, Rifat Hassan. Thank you for reply. Actually there is no bug (we should call it a technical glitch), I was feeding all in small letter rather than capital letter due to this reason it was showing bug. But after your reply I tried this formula again with correct method (all in capital letter) and it worked.

    With regards

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo