Results 1 to 4 of 4

Thread: Sheet Numbering

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sheet Numbering

    Excel 97 - the file regularly gets new sheets added via the Copy Sheet method and old sheets are deleted, so the file keeps about 12-15 sheets. When you check the sheet numbers (the VBA Project list of Excel objects) shows the sheets (beginning with sheet 10) being named Sheet10, Sheet101, sheet1011 . . . sheet10111111111, etc. This CAN'T be good! Can you tell me what's happening?
    Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet Numbering

    When you create a copy of a worksheet, Excel bases the name for the sheet on the original. It really uses the same name but gives an index number to it. The tab name is the same except that it has (2) appended to the original name. In Excel 97, the sheet name had the figure 1 appended for every copy made, so that if you continuously copied the same sheet, the name expanded. If you the copied one of those sheets, the figure 2 would be appended etc. In Excel 2000, it uses the same naming as the sheet tab, i.e. Sheet1(2) for example.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheet Numbering

    I began with a new file and tried to recreate the situation. I left sheets 1, 2, and 3. Copied Sheet 1 to a new sheet that I called Wk1, copied Wk1 and named it Wk2, etc.placing each new sheet left of its predicessor. I had sheets up to wk12. When I checked the VBA list, it generally shows the "real" sheet name and then whatever the user has put on the sheet tab: Sheet1(wk1), Sheet2(wk2) etc. However, Excel listed those with tab names higher than 10 like this: Sheet11(wk10); sheet111(wk11); sheet1111(wk12).

    Then I tried two more times in new Excel files but could not recreate the situation. These times Excel created Sheet10(tabname), Sheet11(tabname), etc.

    Now I'm even more confused than previously.

    Thanks for any input.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Numbering

    This normally happens only when you copy one sheet, copy the copy, copy the copy of the copy, etcetera.

    You can rename the sheetnames in the VBE project explorer:

    - Select the sheet in question from the project explorer (control-R to see the explorer window)
    - make sure you see the properties window (F4)
    - click in the name box and edit the name to match the sheets name as displayed in the main Excel window.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •