How to reset sheet naming in Excel

Question

If I open an Excel workbook, then manually delete "Sheet2" and "Sheet3", when I next click the 'insert worksheet' button it will name the new worksheet "Sheet4".

However, after deleting "Sheet2" and "Sheet3", if I save and re-open the workbook, when I next click the 'insert worksheet' button it will name the new worksheet "Sheet2".

So Excel is storing a 'highest sheet number' variable somewhere, and this is reset when the worksheet is closed. I want to reset this with VBA.

Many thanks and best wishes.

Answer 1

Well you can try hacking it, I was checking out the memory and it looks like there are two integers that keep the worksheet count. But if you delete some sheets the count also decreases so you probably need to widen your search to find the one you're looking for

Once you find the right memory address, try zeroing it (it will probably be 4 bytes, but I would try to just zero the byte that matters).

Here's the function I used to look through the memory (it gets the first 100 bytes after the memory address for worksheets.count, then adds a sheet, and gets the 100 bytes again)

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Sub test2()
    'void ZeroMemory(
    '  [in]  PVOID Destination,
    '  [in]  SIZE_T Length
    ');

    'void CopyMemory(
    '  _In_  PVOID Destination,
    '  _In_  const VOID *Source,
    '  _In_  SIZE_T Length
    ');

    Dim b(0 To 99) As Byte
    Call CopyMemory(b(0), VarPtr(ActiveWorkbook.Worksheets.Count), 100)

    Dim output
    Dim i
    For Each i In b
        output = output & " " & Hex(i)
    Next i

    ActiveWorkbook.Worksheets.Add
    output = output & vbNewLine
    output = output & vbNewLine
    DoEvents

    Call CopyMemory(b(0), VarPtr(ActiveWorkbook.Worksheets.Count), 100)
    For Each i In b
        output = output & " " & Hex(i)
    Next i

    MsgBox output
End Sub

Answer 2

No, there is no way to do this "naturally", without saving, closing and reopening the workbook. The sheets.count property is indeed read-only.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.sheets.count(v=office.15).aspx

But you could change names with vba, as you apparently know. I do agree that it is not really satisfying.

Complement/correction : In excel a sheet object has two different "name" properties : sheet.Name and sheet.CodeName. The Name property is read/write (i.e. can be read, fine, but changed also) and contains the name that you see, that appears on the sheet tab in excel. The CodeName property is read-only... See :

http://msdn.microsoft.com/en-us/library/office/ff837552(v=office.15).aspx

Answer 3

This will change the sheet numbers and leave the names in tact

    Sub BatchChange_WSRefName()
' THIS IS MAINTENANCE CODE ONLY RUN MANUALLY
' Changes the Reference Names for all Worksheets
' in the active Workbook to Sheet + incrementing integer
Dim i As Integer, ws As Worksheet
i = 0
' Change to Temp first to prevent Naming errors
For Each ws In ActiveWorkbook.Worksheets
    i = i + 1
    On Error Resume Next
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Temp" & i
    On Error GoTo 0
Next ws
' Change to Sheet + incrementing integer
i = 0
For Each ws In ActiveWorkbook.Worksheets
    i = i + 1
    On Error Resume Next
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Sheet" & i
    On Error GoTo 0
Next ws
Set ws = Nothing
End Sub

Answer 4

I'm very late to the party, but I was just looking into this today and found another solution.

The sheet numbers (Sheet1, Sheet2, etc.) can be changed manually if you open the Visual Basic window and change it in the Sheet Properties section. This also has the added benefit of resetting the count so the next new sheet will continue in sequence.

image of properties window