Results 1 to 5 of 5

Excel 2010 VBA sheets(array(sheet1, )).copy command crashes excel - but not all the time.

Threaded View

CJH@FSI Excel 2010 VBA... 10-18-2013, 12:43 PM
xladept Re: Excel 2010 VBA... 10-20-2013, 05:38 PM
CJH@FSI Re: Excel 2010 VBA... 10-21-2013, 04:50 AM
CJH@FSI Re: Excel 2010 VBA... 10-21-2013, 07:07 AM
xladept Re: Excel 2010 VBA... 10-21-2013, 02:55 PM
  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    3

    Excel 2010 VBA sheets(array(sheet1, )).copy command crashes excel - but not all the time.

    I have inherited a complex system consisting of multiple excel worbooks. My task is to upgrade the system from Excel 2003 to Excel 2010. It all works OK in excel 2003.

    Background:
    There is a single "processor" workbook that runs throughout the working day - its sole purpose is to open and run other "template" workbooks as and when requested.

    These "template" workbooks are not true excel templates. They are .xlsm files, often with multiple worksheets each sheet with hundreds of cells referencing a shed load of UDFs (all held in a massive VBA add in), excel functions and other cells / sheets within the workbook. Once all the workbook cells have been recalculated some, but not all of worksheets are copied to a new workbook using the following command:

    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10")).Select
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10")).Copy
    The new workbook is tidied up, formatted, saved and closed then the "processor" opens the next "template" and starts again.

    The processor workbook has to run the same template multiple times (albeit with different input parameters) it works OK the first time through but crashes Excel the second time.

    The problem:
    It manages to create the new book but errors on the sheets.copy command:
    Err Nmbr: -2147417848
    Err.Desc: Automation error - The object invoked has disconnected from its clients.

    Shortly after this, Excels displays the "Microsoft Excel has experienced a problem and needs to close" message. Sometimes it completely freezes, sometimes it just shuts down.

    Can anyone explain why this might be?

    I have 100 or so templates to upgrade, all with they same basic processing, so need a solution.

    Many thanks
    Last edited by CJH@FSI; 10-18-2013 at 12:45 PM. Reason: typos

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code works in Windows excel 2003 and 2010 but crashes in Mac excel 2011
    By kiweed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2012, 08:46 PM
  2. [SOLVED] Macro works but crashes Excel 2010
    By Willardio in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-09-2012, 08:49 AM
  3. MS Excel 2010 crashes
    By Richard_G in forum Excel General
    Replies: 0
    Last Post: 07-11-2011, 08:57 AM
  4. MS Excel 2010 crashes
    By Richard_G in forum Excel General
    Replies: 1
    Last Post: 06-25-2011, 12:18 PM
  5. Code Crashes Excel 2010 but works in Excel 2003
    By aaronf316 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2011, 03:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1