+ Reply to Thread
Results 1 to 8 of 8

Speed up macro for copying data from another workbook

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Speed up macro for copying data from another workbook

    The following macro copies the data from a dynamic range. However, for 30 rows it takes more than 10sec. This would possibly contain thousands of rows. How can I improve the speed?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Speed up macro for copying data from another workbook

    Are you working over a network?

    Are you familiar with ADO? Are your files password protected (it doesnt appear to be the case given the code above)? you can actually pull data from files WITHOUT opening them with ADO. I have a macro that rips through about 80 files (5,000 tabs) grabbing about 180MM cells of data in about an hour o_O with ADO. Without ADO it took something like 16+ hours because the files were so slow to open.

    I also noticed Excel 2013 is slower to open than 2010...
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Speed up macro for copying data from another workbook

    Does this help any?

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Speed up macro for copying data from another workbook

    What point is the slowest in the code? Can you walk through it and determine what is taking the longest, then try to find a different approach?

  5. #5
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Re: Speed up macro for copying data from another workbook

    Quote Originally Posted by JOHN H. DAVIS View Post
    Does this help any?

    Please Login or Register  to view this content.
    No, still same if not longer.

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Speed up macro for copying data from another workbook

    And my question?

  7. #7
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Re: Speed up macro for copying data from another workbook

    Used copy/paste instead and works perfect!

  8. #8
    Registered User
    Join Date
    07-31-2014
    Location
    calgary,canada
    MS-Off Ver
    2013
    Posts
    25

    Re: Speed up macro for copying data from another workbook

    Quote Originally Posted by mikeTRON View Post
    Are you working over a network?

    Are you familiar with ADO? Are your files password protected (it doesnt appear to be the case given the code above)? you can actually pull data from files WITHOUT opening them with ADO. I have a macro that rips through about 80 files (5,000 tabs) grabbing about 180MM cells of data in about an hour o_O with ADO. Without ADO it took something like 16+ hours because the files were so slow to open.

    I also noticed Excel 2013 is slower to open than 2010...
    Yes it's over a network but it opens only one file and it doesn't take that long. The problem was with the data transferring but copy/paste works perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copying Data from worksheets of workbook to another workbook with macro.
    By peejaygee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-18-2014, 03:11 PM
  2. [SOLVED] Macro for Opening and copying data to another workbook with password
    By Robotacha2010 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2013, 06:13 AM
  3. Macro for copying data from one workbook to another and deleting
    By harishb63 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-04-2013, 11:53 AM
  4. Macro for copying data from pivot table to other workbook
    By ronlau123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-08-2011, 08:02 AM
  5. Replies: 1
    Last Post: 04-02-2011, 06:39 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