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