Copy/Paste large data using arrays

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
129
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
Application.Calculation = xlManual
and after code
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?
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
perhaps:
VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,335
Messages
5,547,323
Members
410,785
Latest member
phillippaige
Top