clearing VBA worksheet names

M

Maynard

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?
 
B

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)

Maynard said:
I have a VBA program that, when executed, deletes all the worksheets in a
Click to expand...
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?
 
M

Maynard

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.
 
Top