Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    make copy of worksheet (office 2000)

    To all in the know,
    I have a workbook that has several worksheets (each contain a schedule for employees for different weeks) and I continue to make a copy of the latest to wright the new week with just small corrections to start and end times. I have recently run into a bit of a problem when I hold down the ctrl. and drag the worksheet over it does not copy. I have to goback to the several sheets or even back to the original template to make a new workable sheet . I can't figure this one out. All help would be greatly appreciated.
    tia
    Bill

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

    Re: make copy of worksheet (office 2000)

    This is a known bug.

    Excel worksheets have *two* names. the one you see in the XL window and the internal name.

    This internal name starts off with "sheet1".
    After the first copy it is named sheet11, then sheet111, etcetera. You can imagine that problems arise once XL reaches its own maximum number of characters for an internal sheetname.

    To avoid this, as you discovered, don't copy the copy of the copy of...

    To solve it, go to the Visual Basic Editor (VBE, press alt-F11), on the projext explorer (to the left) find the workbook at hand, expand it by clicking the plus sign. Then select the sheet that is troubling you. Lastly, edit the name in the proprties window that should be below the project explorer. Make sure it stays unique within that workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make copy of worksheet (office 2000)

    Another lounger answered this from point of view of VBA but from your description it wasn't apparent that you were working in VBA. (Although the info about the dual worksheet names is still valid.)

    Have you tried right-click on the tab and selecting copy? You would then rename the tab after it's been copied.

    Deb <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

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

    Re: make copy of worksheet (office 2000)

    Hi Deb,

    It is not at all necessary to be working with VBA to cause this problem. Simply control dragging copy of copy of copy... will cause it and the only way to "reset" the seehts internal name is through the VBE as I stated. I don't know if you can rightclick and copy, but I strongly suspect you cannot.

    As a side remark, I thought this problem was fixed in XL 2000 (I cannot reproduce it in my version).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make copy of worksheet (office 2000)

    jujuraf and pieterse ,
    I have tried to "copy" the sheet and still get the same result.... no copy..... Pieterse, I have looked at the module and saw the listings of the the sheets in the workbook with the long numerical name and the corsponding name I have assigned. I am not sure how to change the sheet name and which sheet in the workbook to change. Sorry for sounding so dumb, however, just starting to learn the vba stuff.
    tia,
    Bill

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

    Re: make copy of worksheet (office 2000)

    You need to change all code names of the sheets then. Caution: this sometimes causes trouble, so first backup your file.

    Open the VBE
    click on the first sheet
    make sure the properties window is visible
    click next to the top entry of the properties window which says (name) and change it there.
    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
  •