Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sheet copying (2000/Sp-3)

    Good day all
    I have a fairly large workbook (900k+) that can no longer copy sheets. I am wondering if this is a unique phenomenum or something that has been encountered before.
    I have a Standard sheet with formulas and layout appropriate for the work planned, and many source workbooks with data to be moved to the master (problem) workbook. The approach which I have been using is to :

    1. <LI>open one of the source workbooks containing the source data
      <LI>in the master workbook, copy the Standard sheet to the appropriate location and rename it
      <LI>copy data from the source workbook to the newly created worksheet in the master workbook
      <LI>close the source workbook
      <LI>repeat the cycle with the next source workbook
    Having perfected? the procedure in VBA, with a moderate number of source workbooks, it was time to try with a real world quantity. The problem manifested itself when I had approximately 130 sheets in the workbook. Today on a different machine the failure occurred at the 65 sheet level.
    I believe this to not be a code problem, as I am able to insert a new sheet from the menu bar but cannot copy that sheet with a right click on the tab, though I can move it.

    Any observations will be greatly appreciated
    Regards
    Don

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

    Re: Sheet copying (2000/Sp-3)

    Open one of the problem workbooks.
    Activate the Visual Basic Editor.
    If necessary, expand Microsoft Excel Objects for the relevant workbook in the Project Explorer.
    Each sheet in the workbook is shown with two names, e.g. Sheet2 (Sales Data). The first name is the code name, the one used internally, and the second name is the one you see in the sheet tab. In the Properties window, they are the (Name) and Name properties.
    Excel assigns the code name automatically, and this can get out of hand: Sheet1, Sheet11, Sheet111, etc.

    Do you see very long code names? There is a limit to the length.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet copying (2000/Sp-3)

    No Hans, code names seem to have behaved themselves running from Sheet1 to Sheet 68 without any missing from the sequence.
    Regards
    Don

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet copying (2000/Sp-3)

    The number of sheets in a workbook is limited by available memory - Description of Excel 2000 specifications. Memory issues in Excel are quite complex, see Decision Models - Excel Pages - Memory & Limits for a discussion on the topic. This is just a guess but you may have run out of memory available for Excel to use.

Posting Permissions

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