Copy/Paste large data using arrays

[cortexnotion]

Hello

Part of my tracker is to copy large data ranges to different sheets. I use the below method at the moment but it takes several minutes to run. I have researched I could potentially use arrays to speed up the process. My populated row lengths are variable so I would like to now only copy down to the first blank cell rather than the whole range. The first blank row will be the same in the entire code once found.

Sheets("DataWork").Range("A3:A7000").Value = Sheets("DataInput").Range("A3:A7000").Value
    Sheets("Projects").Range("A3:A7000").Value = Sheets("DataInput").Range("A3:A7000").Value
    Sheets("Dates").Range("A3:A7000").Value = Sheets("DataInput").Range("A3:A7000").Value
       
    Sheets("DataWork").Range("B3:B7000").Value = Sheets("DataInput").Range("C3:C7000").Value
    Sheets("Projects").Range("B3:B7000").Value = Sheets("DataInput").Range("C3:C7000").Value
    Sheets("Dates").Range("B3:B7000").Value = Sheets("DataInput").Range("C3:C7000").Value
           
    Sheets("DataWork").Range("C3:C7000").Value = Sheets("DataInput").Range("D3:D7000").Value
    Sheets("Projects").Range("C3:C7000").Value = Sheets("DataInput").Range("D3:D7000").Value
    Sheets("Dates").Range("C3:C7000").Value = Sheets("DataInput").Range("D3:D7000").Value
           
    Sheets("DataWork").Range("D3:D7000").Value = Sheets("DataInput").Range("B3:B7000").Value
    Sheets("Projects").Range("D3:D7000").Value = Sheets("DataInput").Range("B3:B7000").Value
    Sheets("Dates").Range("D3:D7000").Value = Sheets("DataInput").Range("B3:B7000").Value
           
    Sheets("DataWork").Range("E3:E7000").Value = Sheets("DataInput").Range("N3:N7000").Value
    Sheets("Projects").Range("E3:E7000").Value = Sheets("DataInput").Range("N3:N7000").Value
    Sheets("Dates").Range("E3:E7000").Value = Sheets("DataInput").Range("N3:N7000").Value
       
    Sheets("DataWork").Range("F3:F7000").Value = Sheets("DataInput").Range("J3:J7000").Value
    Sheets("Projects").Range("F3:F7000").Value = Sheets("DataInput").Range("J3:J7000").Value
    Sheets("Dates").Range("F3:F7000").Value = Sheets("DataInput").Range("J3:J7000").Value
           
    Sheets("DataWork").Range("G3:G7000").Value = Sheets("DataInput").Range("E3:E7000").Value
    Sheets("Projects").Range("G3:G7000").Value = Sheets("DataInput").Range("E3:E7000").Value
    Sheets("Dates").Range("G3:G7000").Value = Sheets("DataInput").Range("E3:E7000").Value
       
    Sheets("DataWork").Range("H3:H7000").Value = Sheets("DataInput").Range("V3:V7000").Value
    Sheets("Projects").Range("H3:H7000").Value = Sheets("DataInput").Range("V3:V7000").Value
    Sheets("Dates").Range("H3:H7000").Value = Sheets("DataInput").Range("V3:V7000").Value
       
    Sheets("DataWork").Range("I3:I7000").Value = Sheets("DataInput").Range("T3:T7000").Value
       
    Sheets("Projects").Range("I3:I7000").Value = Sheets("DataWork").Range("J3:J7000").Value
    Sheets("Projects").Range("J3:J7000").Value = Sheets("DataWork").Range("K3:K7000").Value
    Sheets("Projects").Range("K3:K7000").Value = Sheets("DataWork").Range("L3:L7000").Value
    Sheets("Projects").Range("L3:L7000").Value = Sheets("DataWork").Range("M3:M7000").Value
    Sheets("Projects").Range("M3:M7000").Value = Sheets("DataWork").Range("N3:N7000").Value
       
    Sheets("Projects").Range("N3:N7000").Value = Sheets("DataWork").Range("P3:P7000").Value
    Sheets("Projects").Range("O3:O7000").Value = Sheets("DataWork").Range("Q3:Q7000").Value
    Sheets("Projects").Range("P3:P7000").Value = Sheets("DataWork").Range("R3:R7000").Value
    Sheets("Projects").Range("Q3:Q7000").Value = Sheets("DataWork").Range("S3:S7000").Value
    Sheets("Projects").Range("R3:R7000").Value = Sheets("DataWork").Range("T3:T7000").Value
    Sheets("Projects").Range("S3:S7000").Value = Sheets("DataWork").Range("U3:U7000").Value
    Sheets("Projects").Range("T3:T7000").Value = Sheets("DataWork").Range("V3:V7000").Value
    Sheets("Projects").Range("U3:U7000").Value = Sheets("DataWork").Range("AY3:AY7000").Value

Thanks, Chris

 

[Jon von der Heyden]

You are actually processing arrays by virtue of using range.Value = range.Value. Whilst we could do something a little tidier, I doubt you'll see much performance upside. Performance may however be improved by toggling calculation. E.g. at start of code:

Application.Calculation = xlManual

and after code

Application.Calculation = xlAutomatic

With regards to identifying last row, does this need to be the same last row reference for each sheet, regardless of column, or would each column have a different last row reference?

 

[cortexnotion]

Hi @Jon von der Heyden, thanks for your quick reply.

For the last row, the last row used will be identical regardless of sheets.

 

[Jon von der Heyden]

perhaps:

    Dim lngLastRow          As Long

    lngLastRow = Sheets("DataInput").Range("A3").End(xlDown).Row
       
    Sheets("DataWork").Range("A3:A" & lngLastRow).Value = Sheets("DataInput").Range("A3:A" & lngLastRow).Value
    Sheets("Projects").Range("A3:A" & lngLastRow).Value = Sheets("DataInput").Range("A3:A" & lngLastRow).Value
    Sheets("Dates").Range("A3:A" & lngLastRow).Value = Sheets("DataInput").Range("A3:A" & lngLastRow).Value

 

[cortexnotion]

Thank you @Jon von der Heyden