Reset Excel sheet count?

[tpoynton]

Greetings - i have the following bit of code to delete all but the visible sheet. it works fine, but I was wondering if there is a way to set the sheetcount back to 1 as part of this.

Right now, if I have sheets named like "sheet1", "sheet2" etc all the way up to "sheet30" and I run this procedure, it starts again at number 31 after sheets 1-30 are deleted. is there a way to reset this back to the lowest possible number? restarting excel does this, but I am wondering if it can be done through VBA.

Public Sub DeleteSheets()
    Dim currentSheet As String
    Dim varAnswer As String
    Dim wsSheet As Worksheet
    Dim strName As String
    currentSheet = ActiveSheet.Name
    varAnswer = MsgBox("This will delete all sheets except for the sheet named '" _
   + currentSheet + "'" + (Chr(13)) + (Chr(13)) + _
    "Click Yes to irreversibly erase the other sheets, or No to cancel this procedure", _
    vbYesNo, "Warning - This Procedure Can Not Be Undone!")
    If varAnswer = vbNo Then
        Exit Sub
    Else:
        Application.DisplayAlerts = False
        With ActiveSheet
            For Each wsSheet In Worksheets
                strName = wsSheet.Name
                If strName <> currentSheet Then
                    wsSheet.Delete
                End If
            Next wsSheet
        End With
    end if
    Application.DisplayAlerts = True
End Sub

What happens is that i have other procedures which add new sheets through VBA using worksheets.add. It is not that big of a deal, but I am curious!

[austenr]

Is this what you want?

 Public Sub DeleteSheets()
    Dim currentSheet As String
    Dim varAnswer As String
    Dim wsSheet As Worksheet
    Dim strName As String
    currentSheet = ActiveSheet.Name
    varAnswer = MsgBox("This will delete all sheets except for the sheet named '" + _
    currentSheet + "'" + (Chr(13)) + (Chr(13)) + "Click Yes to irreversibly erase " & _
    "the other sheets, or No to cancel this procedure", vbYesNo, _
    "Warning - This Procedure Can Not Be Undone!")
    If varAnswer = vbNo Then
        Exit Sub
    Else:
        Application.DisplayAlerts = False
        With ActiveSheet
            For Each wsSheet In Worksheets
                strName = wsSheet.Name
                If strName <> currentSheet Then
                    wsSheet.Delete
                End If
            Next wsSheet
        End With
    End If
    ActiveSheet.Name = "Sheet1"
    Application.DisplayAlerts = True
End Sub

[tpoynton]

Thanks for the reply austenr, and my apologies for not being clear!

If I am not mistaken, that will set the undeleted sheet to be named "Sheet1". I dont want to change the name of the remaining sheet, but do want all future sheets that are added to start at the lowest possible number. right now, it picks up where it left off. if Excel is restarted, it goes back to using the lowest possible number - i would like to, if possible, reset that counter without restarting excel!

I am constantly amazed at how many settings there are, and I am pretty sure it is available somewhere!

[mdmackillop]

Hi both,
Can you please use line breaks in the code, or I'll need to get a second screen!

[tpoynton]

I was going to add line breaks, but someone has beat me to it!

I'll also work on creating a file to upload...

[mdmackillop]

Yeah.
I did that.
I see your problem, but I don't know if there is a "real" solution, but here's a possible workaround. It works with one error, but not two. Add the code to ThisWorkbook module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    On Error GoTo errh
    Sh.Name = "Sheet" & Sheets.Count
    Exit Sub
    errh:
    Sh.Name = "Sheet" & Sheets.Count + 1
End Sub

[tpoynton]

I just saw your post, and will play with that soon. here is a demo file to tinker with...i need to take a break, but will check in again tomorrow...

THANKS!

PS - noticed the sheets.count property is read only...that may be a problem!

[geekgirlau]

Personally I never leave a sheet named "Sheet1" anyway - I prefer to use a name that tells me what's on the sheet!

[tpoynton]

attached is a solution i can live with; i was hoping to not have to go through all of my code again, but i can use search and replace to use the addsheets function instead of worksheets.add.

i'll mark as solved, but if anyone comes up with a more elegant solution, i'm all ears...

[johnske]

Option Explicit
 
Sub DeleteSheets()
    Dim Answer As String, Sheet As Worksheet
    Answer = MsgBox("This will delete all sheets except for the sheet named '" & _
    ActiveSheet.Name & "'" & vbNewLine & vbNewLine & _
    "Click Yes to irreversibly erase the other sheets, or No to cancel " & _
    "this procedure", vbYesNo, "Warning - This Procedure Can Not Be Undone!")
    If Answer = vbNo Then
        Exit Sub
    Else
        Application.DisplayAlerts = False
        For Each Sheet In Worksheets
            If Sheet.Name <> ActiveSheet.Name Then Sheet.Delete
        Next
        Application.DisplayAlerts = True
    End If
End Sub
 
Sub AddSheets()
    Dim N As Long
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "EZA" & Sheets.Count - 1
    For N = 1 To Sheets.Count
        ThisWorkbook.VBProject.VBComponents(Sheets(N).CodeName).Name = "Sheet" & N
    Next
End Sub

FYI, although it's possible to use the "+" symbol for concatenation, it is neither good nor accepted practice. It's best to reserve this symbol for additions only and to use the "&" symbol for concatenation. Here is what's said in the VBA Help files regarding this

Remarks

When you use the + operator, you may not be able to determine whether addition or string concatenation will occur. Use the & operator for concatenation to eliminate ambiguity and provide self-documenting code.

Also, for new lines it's better practice to use vbNewLine (or even vbCr, or vbLf, or vbCrLf) rather than Chr(13)

[Bob Phillips]

Also, for new lines it's better practice to use vbNewLine (or even vbCr, or vbLf, or vbCrLf) rather than Chr(13)

vbNewLine is best. Macs recognise vbNewLine.

[tpoynton]

THANKS - that works quite nicely! regarding my bad coding habits, I have a bunch...thanks for pointing them out as it helps me develop new and better ones!

Regarding chr(13), I looked all over to find out how to add a blank line in messageboxes and found that. It also works on Macs, but is certainly not intuitive! thanks again!

[johnske]

THANKS - that works quite nicely! regarding my bad coding habits, I have a bunch...thanks for pointing them out as it helps me develop new and better ones!

Regarding chr(13), I looked all over to find out how to add a blank line in messageboxes and found that. It also works on Macs, but is certainly not intuitive! thanks again!

Not a prob, we're all here to learn. Happy holidays!