Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling sheet number during sheet add
I have a macro that continually deletes and adds a worksheet. Every
time it does this, the default sheet number that Excel assigns increments (Sheet1, Sheet2 ..etc). I only have 3 sheets in my workbook, but since I am constantly recreating the 3rd sheet, the internal Excel-assigned SheetXXX name keeps incrementing. This is NOT the name of the sheet (which I can change), but the SheetXXX name that Excel assigns to a new sheet. 1) Is there a way to tell Excel what default sheet number to use. OR 2) Is there a way to reset the new sheet counter in VBA? The default new sheet counter continually goes up even if I close and open Excel. Currently, I'm at Sheet145... but I'm guessing that something will eventually max out... maybe at Sheet255? Any ideas? Thanks. John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling sheet number during sheet add
Thanks for the reply.
The problem is not the worksheet name, it's the worksheet (name) property that Excel internally uses to track worksheets. You see this in the Project Explorer, for example: Sheet1 (MyWorksheetName1) Sheet2 (MyWorksheetName2) Sheet145 (MyWorksheetName3) It's the Sheet145 part that I'm trying to control... You can manually change the (name) property to anything in the property window, but I can't figure out to change the (name) property from code or how to reset the tracking number that Excel is using to assign the next sheet it creates. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling sheet number during sheet add
See if this helps. You will have to goto toolsmacrosecuritytrusted
publisherstrust access. Sub addsht() Sheets.Add after:=Sheets(Sheets.Count) On Error Resume Next For i = 1 To Sheets.Count With ActiveSheet ..Name = "sheet" & i ThisWorkbook.VBProject.vbcomonents.codename = "Sheet" & i End With Next i End Sub -- Don Guillett SalesAid Software "robotman" wrote in message ups.com... Thanks for the reply. The problem is not the worksheet name, it's the worksheet (name) property that Excel internally uses to track worksheets. You see this in the Project Explorer, for example: Sheet1 (MyWorksheetName1) Sheet2 (MyWorksheetName2) Sheet145 (MyWorksheetName3) It's the Sheet145 part that I'm trying to control... You can manually change the (name) property to anything in the property window, but I can't figure out to change the (name) property from code or how to reset the tracking number that Excel is using to assign the next sheet it creates. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to lookup a sheet, number, then display in origonal sheet | New Users to Excel | |||
Controlling tab order on a sheet to include AX combos | Excel Programming | |||
Creating and Controlling a User data Entry Sheet | Excel Discussion (Misc queries) | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions |