Controlling sheet number during sheet add

[robotman]

I have a macro that continually deletes and adds a worksheet. Every
time it does this, the default sheet number that Excel assigns
increments (Sheet1, Sheet2 ..etc). I only have 3 sheets in my
workbook, but since I am constantly recreating the 3rd sheet, the
internal Excel-assigned SheetXXX name keeps incrementing. This is NOT
the name of the sheet (which I can change), but the SheetXXX name that
Excel assigns to a new sheet.

1) Is there a way to tell Excel what default sheet number to use.

OR

2) Is there a way to reset the new sheet counter in VBA?

The default new sheet counter continually goes up even if I close and
open Excel. Currently, I'm at Sheet145... but I'm guessing that
something will eventually max out... maybe at Sheet255?

Any ideas?

Thanks.

John

 

[Don Guillett]

You might like this.
Sub addsht()
Sheets.Add after:=Sheets(Sheets.Count)
On Error Resume Next
For i = 1 To Sheets.Count
ActiveSheet.Name = "sheet" & i
Next i
End Sub

 

[robotman]

Thanks for the reply.

The problem is not the worksheet name, it's the worksheet (name)
property that Excel internally uses to track worksheets.

You see this in the Project Explorer, for example:

Sheet1 (MyWorksheetName1)
Sheet2 (MyWorksheetName2)
Sheet145 (MyWorksheetName3)

It's the Sheet145 part that I'm trying to control...

You can manually change the (name) property to anything in the
property window, but I can't figure out to change the (name) property
from code or how to reset the tracking number that Excel is using to
assign the next sheet it creates.

Any ideas?

 

[Don Guillett]

See if this helps. You will have to goto tools>macro>security>trusted
publishers>trust access.

Sub addsht()
Sheets.Add after:=Sheets(Sheets.Count)
On Error Resume Next
For i = 1 To Sheets.Count
With ActiveSheet
..Name = "sheet" & i
ThisWorkbook.VBProject.vbcomonents.codename = "Sheet" & i
End With
Next i
End Sub