clearing VBA worksheet names

[Guest]

I have a VBA program that, when executed, deletes all the worksheets in a workbook and creates new ones. Each time I execute this, the worksheet names ("Sheet1", "Sheet2", etc) aren't cleared...that is, if Sheet1 through Sheet25 are deleted, the new sheets start at Sheet26...even if I close Excel and reopen the workbook. How do I get the newly created sheets to restart at Sheet1?

 

[Bob Phillips]

Something like?

Dim sh As Worksheet
Dim i As Long

On Error Resume Next
Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
sh.delete
Next
ActiveSheet.Name = "Sheet1"
i = 2
Worksheets.Add.Name = "Sheet" & i
i = i + 1
Worksheets.Add.Name = "Sheet" & i


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a VBA program that, when executed, deletes all the worksheets in a

workbook and creates new ones. Each time I execute this, the worksheet
names ("Sheet1", "Sheet2", etc) aren't cleared...that is, if Sheet1 through
Sheet25 are deleted, the new sheets start at Sheet26...even if I close Excel
and reopen the workbook. How do I get the newly created sheets to restart
at Sheet1?

 

[Guest]

Doesn't Worksheet.Name change the worksheet name on the Excel side, and not on the VBA side? I'm not worried about the Excel side, but the VBA side. In other words, I am creating a new worksheet with Worksheet.Name = "MyData" (for example)...but on the VBA side, it's (Name) is Sheet592. I'm just worried I'm going to bump up against some limit if I don't practice clearing out the worksheet names on the VBA side.

Thanks.