How do you renumber sheets in a large Excel workbook?

With an Excel workbook with a number of sheets in a workbook, it can be difficult to rename the sheets manually if you insert or delete a sheet:

 Intro-A   <<== Skip this one, please
 Intro-B   <<== Skip 
 Intro-C   <<== Skip 
 Main-1    <<== Start renumbering here
 Main-2
 Main-3
           <<== Deleted
 Main-5
 Main-6...

and so on. If I insert or delete a sheet in the sequence, I have to renumber the sheets manually. How can this be done with VBA to save time?

Solution 1:

Use A Macro

I found the answer on a blog, "Notes of a Sysadmin" from Neseef.com, which did most of what I need. I added the ability to skip the introductory sheets, prompt for the prefix, and avoid name collisions that occur when a sheet has been inserted.

The macro doesn't need to be in the same workbook. It can be in a different workbook, as long as both are open at the same time.

• ⚠ Caution: Save the Workbook before beginning work!
• You can add the macro below to your workbook, or you can create a New workbook -- save the workbook with the macro as *.xlsm (Macro-Enabled Workbook).
• As usual, Alt-F11 : Insert : Module and paste the macro below.
• Return to the workbook with Alt-F4.
• Run the macro with Alt-F8 and "OK".

' ** Rename Sheets ** 2020-09-10
' If you need to renumber your sheets sequentially, with a prefix,
' Like "AA-1, AA-2, AA-3" or "XX-A, XX-1, XX-2"..
' Suggested by this method: https://neseef.com/2019/03/31/excel-rename-multiple-worksheets-vbs/
' Use only on saved worksheets.  As-is.

Sub RenamingSheets()

Dim myPrefx As String

myStartSheet = InputBox("What sheet to start with? (1=first)", "Renaming Sheets")
myPrefx = InputBox("What prefix should the sheet names have?  (`BG-`, `Sheet`, etc.)", "Renaming Sheets")
myNum = InputBox("What's the first number you want to name the sheets?", "Renaming Sheets")

'Clear the names so there are no collisions
'(Temporarily set the name to a hopefully unique name)

For ws = myStartSheet To Worksheets.Count
    Sheets(ws).name = "Foo" & Worksheets.Count + ws
Next ws

'Now renumber

For ws = myStartSheet To Worksheets.Count
    Sheets(ws).name = myPrefx & myNum
    myNum = myNum + 1
Next ws

End Sub