Open excel file. Then click Alt + F11

VBS screen will open. On the left panel right click on VBAProject(YourFileName)

right click > insert > module

To rename all worksheets copy paste the following script. 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 we run the above script it will ask for a starting number. If I give 25. Then the script will rename all worksheets as : BM-25, BM-26, BM-27… so on. You can change the BM to whatever you want in the script.

To replace part of the worksheet 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 we run the above script it will ask for two things.

1. What part of the name you want to replace. Give the word (or part of name) we want to replace here.
2. Then it will ask for what you want to replace it with. if your intention is to remove the part just leave this blank and click ok. or give the value you want to replace with.

HOW TO RUN

• After pasting the script, click the Green Play button on the tool panel.
• Click on RUN on title bar > then click Run Sub/UserForm

Close the VBS window.

now the names will be changed.