Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Sheets Failed (Excel 2000)

    Howdy...I figure I'm either bumping up into some limitations in Excel, or (more likely) some limitations in my coding ability! Perhaps someone in the Lounge could help...

    I have a spreadsheet with a tab called MasterDoc. The associated VBA Code has a loop that performs some calculations/updates to this MasterDoc worksheet, and then copies this MasterDoc worksheet into a new workbook, renames the tab in the new workbook, and then repeats this loop for the next store. It should loop through 12 stores.

    Windows("Sale 2002 - v.1.10.xls").Activate
    Sheets("MasterDoc").Select
    'Copy the Master worksheet to the interim Store Qty CommunicationMaster file
    'for each store, then save that file with the final unique filename including Region, District and Store
    Sheets("MasterDoc").Copy Before:=Workbooks("Store Qty CommunicationMaster.xls"). _
    Sheets(1)

    This works perfectly for the first 8 times through this iteration, but then fails on the 9th iteration...the error message is "Run-time Error '1004': Copy method of worksheet class failed." So when it crashes there are nine worksheets - Sheet1, plus my 8 copied/renamed worksheets, and it seems to fail when attempting to add the next worksheet. The Visual Basic Editor is pointing to the last line in the code sample shown above.

    Is there some sort of limit in Excel to the number of worksheets I can add this way? More likely, I've just chosen some bad coding practices! Can anyone provide any suggestions?

    Thanks,
    Trev.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Sheets Failed (Excel 2000)

    It may have to do something with the code name of the sheets.

    Excel worksheets have two names: the name you see on the worksheet tab and an internal code name. This code name is assigned automatically. If a lot of copying has been going on, this code name may become too long.

    You can see and modify the code name in the Visual Basic Editor:

    Switch to the VBE (Tools/Macro/Visual Basic Editor or Alt+F11).
    In the Project Explorer, expand your workbook, then Microsoft Excel Objects.
    For each worksheet, you'll see something like Sheet11111 (MasterDoc).
    In this example, MasterDoc is the tab name, and Sheet11111 is the code name.
    If you see very long code names, modify them in the Properties window.
    The (Name) property is the code name, while the Name property is the tab name.

    Unfortunately, the code name is a read-only property in VBA (in Excel 97, probably hasn't changed in Excel 2000), so you can't set it in your macro.

    HTH,
    Hans

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

    Re: Copy Sheets Failed (Excel 2000)

    Hans,

    The codename may be readonly officially, but I find the following code works provided of course that the new name does not already exist in the project.

    Dim ws As Worksheet
    Set ws = ActiveSheet
    ThisWorkbook.VBProject.VBComponents(ws.CodeName).P roperties("_CodeName").Value = "NewName"

    Andrew

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Sheets Failed (Excel 2000)

    Thanks Hans and Andrew...in the Project Explorer, the sheet names are all a max of 3 characters after the word 'sheet'.

    I'll see if I can recreate this problem in a non-confidential workbook, and if so, I'll post it back here...in the meantime, any other suggestions are very welcomed!!!

    Cheers,
    Trev.
    Attached Images Attached Images

Posting Permissions

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