clearing VBA worksheet names
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?
|
clearing VBA worksheet names
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" wrote in message ... 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? |
clearing VBA worksheet names
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. "Bob Phillips" wrote: 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" wrote in message ... 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? |
All times are GMT +1. The time now is 11:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com