Reset Sheet Count -Second Request Any Help Appreciated

[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

 

[David Heaton]

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

I'm not sure how you are adding your sheets but this will create a new
sheet and name it for you.

ThisWorkbook.Worksheets.Add.Name = "I'm New"

hth

regards

David

 

[Joerg Mochikun]

Do you get the same result when you do the same operations manually? I tried
to reproduce the behaviour you described but found that Excel (2003) always
tries to name a new worksheet with the lowest default number. So if you have
renamed Sheet1 to S1 and Sheet2 to S2, but keep an original Sheet3, the
next new sheet would be named Sheet1 by default. So maybe it has something
to do with your code. If it really bothers you then your code should create
the new sheet already with the desired name (Sheets.Add.Name =
"MySheetName") instead of creating a sheet and then renaming it.

Joerg

 

[Peggy Shepard]

Hi Bill,

Save the workbook as a new name, close the file, rename the workbook back to
its original name.

Peggy