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.
- What part of the name you want to replace. Give the word (or part of name) we want to replace here.
- 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.