Renaming Worksheets in Excel using VBA Script

To rename all worksheets in Excel, follow these steps:

  1. Open Excel file.
  2. Press Alt + F11 to open the VBS screen.
  3. In the left panel, right-click on VBAProject(YourFileName).
  4. Then, right-click > insert > module.

Renaming All Worksheets

To rename all worksheets, copy and paste the following script into the module. Then click Run:

Sub RenamingSheets()
nmbr = InputBox("What's the first number you want to name the sheets?", "Renaming Sheets")
For ws = 1 To Worksheets.Count
Sheets(ws).Name = "BM-" & nmbr
nmbr = nmbr + 1
Next ws
End Sub

When you run the above script, it will ask for a starting number. For example, if you input "25", it will rename the worksheets as:

BM-25, BM-26, BM-27, ... and so on. You can change "BM" to whatever you prefer in the script.

Replacing Part of the Worksheet Name

If you need to replace part of a worksheet's name, use the following script:

Sub Find_replace_sheet_name()
Dim xNum As Long
Dim xRepName As String
Dim xNewName As String
Dim xSheetName As String
Dim xSheet As Worksheet
xRepName = Application.InputBox("Please type in the word you will replace:", "Replace this word", , , , , , 2)
xNewName = Application.InputBox("Please type in the word you will replace with:", "Replace with this value", , , , , , 2)
If xRepName = "false" Or xNewName = "false" Then Exit Sub
On Error GoTo ExitLab
For Each xSheet In ActiveWorkbook.Sheets
xSheetName = xSheet.Name
xNum = InStr(1, xSheetName, xRepName)
If xNum > 0 Then
xSheet.Name = Replace(xSheetName, xRepName, xNewName)
End If
ExitLab:
Next
End Sub

When you run the above script, it will ask for two inputs:

  1. What part of the name you want to replace. Enter the word (or part of the name) you wish to replace.
  2. What you want to replace it with. If you intend to remove the part, just leave this blank and click OK, or provide the replacement value.

How to Run the Script

  • After pasting the script, click the green play button on the tool panel.
  • Alternatively, click on Run in the title bar, and then click Run Sub/UserForm.

Close the VBS window, and now the worksheet names will be changed.