PDA

View Full Version : Re-indexing Excel Sheets



vmaxer
04-06-2015, 07:03 PM
This code re-numbers all worksheets to back-fill the empty sheet indexes when a workbook is saved.
Excel will start indexing at the next available (new) sheet index when workbook is re-opened.

I too wanted a way to re-index the worksheets so I can dynamically add/delete many worksheets within VBA. The benefit to dynamically adding and deleting sheets vs having predefined worksheets hidden is file size and time to load the workbook.

My workbook sheets contain a lot of data... each worksheet has repeated formats and code embedded. I found it best to keep one 'template' worksheet hidden, then copy it and rename the copied sheet within VBA code. The copy/rename method duplicates the code and sheet format from the template sheet while keeping the sheet count (and data file size) to a minimum.

Problem is... deleting the newly made sheets when I'm done with them makes holes in Excel's sheet indexing. The last sheet index grows and grows every time the workbook is re-open.

I wrote this solution to be executed when a workbook is saved. Tested to work OK in Excel 2003.
- It should be placed in the VBA editor's 'ThisWorkbook' object.
- This is meant to be more of a maintenance tool... and there is no error trapping in this code (feel free to add it!). All worksheets are temporally re-indexed above the highest existing sheet index, so make sure there is enough index room to do that.

The way it works:
1) Function calls when workbook was chosen to save.
2) Find the highest number sheet in your workbook.
3) Make room to renumber all sheets low, to do this... Renumber all sheets to be greater than the highest existing sheet number.
4) Now that all low sheet numbers are free - renumber them again them to start at index=1.




Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'========================================================================== =====================
'- Renumber all sheets to back-fill empty sheet indexes before saving workbook.
'- Allows Excel to begin indexing sheets to next lowest available sheet index when re-opened.
'- Needs Tools/Reference "Microsoft Visual Basic for Applications Extensibility"
'========================================================================== =====================

Dim ws As Worksheet
Dim i As Integer

Dim sThisSheetCodeName As String
Dim iThisSheetNumber As Integer
Dim iMaxSheetNumber As Integer

Application.ScreenUpdating = False

'1st - Find the highest sheet number
For Each ws In ActiveWorkbook.Worksheets
sThisSheetCodeName = ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName").Value
iThisSheetNumber = Right(sThisSheetCodeName, Len(sThisSheetCodeName) - 5)
If iThisSheetNumber > iMaxSheetNumber Then iMaxSheetNumber = iThisSheetNumber
Next

'2nd - Renumber all sheets to be > than highest sheet number
i = iMaxSheetNumber + 1
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName").Value = "Sheet" & i
Next

'3rd - Now that all sheets are > max sheet number. Renumber all sheets to start with 1
i = 0
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName").Value = "Sheet" & i
Next

Application.ScreenUpdating = True

End Sub





I'm new to this message board and wanted to give back.

Credit goes to BrianB for his message on message board for exposing me to the code line:
' ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName").Value '


Thanks Brian, this code line was key in finding a solution for my sheet indexing problem!