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