Reset Sheet Count

[Bill]

Hopefully just a quick question, I have some code that gets run
everyday and it inserts sheets in a workbook and renames each sheet to
specific value. The next day the same code gets run and it deletes the
worksheets that i added the previous day and adds some new worksheets,
again changing the name of the sheets to a specific value and so on
and so forth each day of the week.

This works fine, however when the code inserts the new worksheet I
have noticed that the default name that excel gives the sheet(before i
rename it) is getting higher and higher. When i insert a new sheet now
the default name is up to sheet 541.

So my question is, how do I reset it so that each time i run the code
it will the sheets will be inserted with the name Sheet1 or a
similiarly low number ?

Thanks for any help

 

[Andy Smith]

Your macro should add and rename each sheet with code like:

With Me.Worksheets.Add(xlWorksheet)
.Name = "Sheet" & Me.Worksheets.Count
End With

Then you don't have to figure out what its name is.