+ Reply to Thread
Results 1 to 4 of 4

Macro Very Slow to Copy Lots of Data

  1. #1
    Registered User
    Join Date
    05-21-2013
    Location
    MTL
    MS-Off Ver
    Excel 2007
    Posts
    16

    Macro Very Slow to Copy Lots of Data

    Hello everyone,

    I have 4 different sheets in one workbook from which I need to copy the first 5 columns. Not entirely though, only the rows that contain values (i.e. not the rows with all errors or all zeros). Note that these rows are all at the bottom of the sheets. Then I need to paste these values all in one "consolidated" sheet. I have done it the following way:

    i = 2
    Do Until IsError(fcst.Cells(i, 1)) And IsError(fcst.Cells(i, 2)) And IsError(fcst.Cells(i, 4))

    conso.Range("A65536").End(xlUp).Offset(1, 0) = fcst.Cells(i, 1).Value
    conso.Range("B65536").End(xlUp).Offset(1, 0) = fcst.Cells(i, 2).Value
    conso.Range("C65536").End(xlUp).Offset(1, 0) = fcst.Cells(i, 3).Value
    conso.Range("D65536").End(xlUp).Offset(1, 0) = fcst.Cells(i, 4).Value
    conso.Range("E65536").End(xlUp).Offset(1, 0) = fcst.Cells(i, 5).Value

    i = i + 1

    Loop

    And I did the same for the three other pages (--> four loops in a row)

    (n.b: I defined all my sheets at the beginning of the code like such:
    Set conso = Sheets("Consolidation")
    Set fcst = Sheets("Forecast Formatted")
    Set budget = Sheets("Budget Formatted")
    Set act = Sheets("Actuals Formatted")
    Set PFYact = Sheets("PastFYActualsFormatted"))

    My problem is that when I run the macro, it takes an incredibly long time. My question, therefore, is whether there is something I could do to my code to make more efficient/faster, or if it may be that my computer is slow or something.

    Thanks for any help,

    Corentin

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Macro Very Slow to Copy Lots of Data

    You are transferring cell values across one at a time which is costing you time and you are repeatedly calculating the last row of your source data.. You should only need to calculate the last row of your source and destination sheets once and the transfer is a one shot deal by using ranges.
    Elegant Simplicity............. Not Always

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro Very Slow to Copy Lots of Data

    Are the values you want to copy constants or results from formulas?
    If posting code please use code tags, see here.

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Macro Very Slow to Copy Lots of Data

    Did I mention that my bro lives in Dollar?

  5. #5
    Registered User
    Join Date
    05-21-2013
    Location
    MTL
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Macro Very Slow to Copy Lots of Data

    Norie: They are formulas, and I want to extract only the values.

    Andy Litch: Can you give me a quick example?

    Thank you guys

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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