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! :)