Avoiding copy/paste, selection to transfer data in VBA

[ItalianPlatinum]

Looking for a way to transfer a set of data over to another sheet after i set a filter without copy, selecting or pasting.

1. Sheet 1: filtered to not show blanks
   1. Want to transfer the data left showing
2. Sheet 2: Where I want to transfer the data to
3. Columns match up like this:
   1. Column A to Column A13 down
   2. Column I to Column B13 down
   3. Column J to Column C13 down
   4. so on and so on.....

 

[ZVI]

Hi,

Do all in VBA, including blank rows skipping.

Copy source data columns into VBA arrays.
Loop those arrays, skip empty data in arrays, populate resulting VBA array, copy data from the resulting array to the destination range.

But take into the account a total size of data in the source arrays (refer to your e.t.c. and unknown rows count).
Big size slows speed down, data can be swapped on disk (it's very slow), or even hangs the code.
In such a case process the source data chunk by chunk.

 

[ItalianPlatinum]

Hi,

Do all in VBA, including blank rows skipping.

Copy source data columns into VBA arrays.
Loop them, skip empty rows, populate destination array, copy result to the destination range.

But take into the account a total size of data in the source arrays (refer to your e.t.c. and unknown rows count).
Big size slows speed down, data can be swapped on disk (it's very slow), or even hangs the code.
In such a case process the source data chunk by chunk.

So the data could be large that is where I was seeing if there is a quicker way and copying using clipboard. Or are you proposing copy all the data over then in the new sheet purge the data i want to see? Wouldn't be sure what is the best avenue and most efficient

 

[ZVI]

... Or are you proposing copy all the data over then in the new sheet purge the data i want to see?

No, my proposing is in copy data of the source columns (not all the data from Sheet1) into VBA arrays (not to the Sheet2), prepare resulting data in VBA array and after that copy that data from resulting array to the Sheet2. See also my warnimg about big size.
You've highlighted a problem with a big data in a clipboard - try copying the filtered columns one by one, this can (or can't) speed up, but testing is required.

P.S. See updated words in Bold in my previous post #2

 

[ZVI]

How long does it take now using copy-paste?
Is there a separate column in Sheet1 to filter data without empty rows?

 

[ItalianPlatinum]

How long does it take now using copy-paste?
Is there a separate column to filter out empty rows?

No timer yet not built am worried if I went the copy/Paste route I would lose time seeing I plan to do a lot of steps. I wanted to leverage the below to transfer the date but I dont believe it will work with filtered data. The below would transfer all?

With Sheets("TREND")
  rws = .Range("A2:A2").End(xlDown).row - 1
  Sheets("COMPARE").Range("A13").Resize(rws, 1).Value = .Range("A13").Resize(rws).Value
End With

 

[ZVI]

No timer yet not built am worried if I went the copy/Paste route I would lose time seeing I plan to do a lot of steps. I wanted to leverage the below to transfer the date but I dont believe it will work with filtered data. The below would transfer all?

With Sheets("TREND")
  rws = .Range("A2:A2").End(xlDown).row - 1
  Sheets("COMPARE").Range("A13").Resize(rws, 1).Value = .Range("A13").Resize(rws).Value
End With

That code copies all data, including data hidden by filter.

 

[ZVI]

Try this instead:

Sub Test1()
  
  With Sheets("TREND")
    .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy Sheets("COMPARE").Range("A13")
  End With
  
End Sub

 

[ZVI]

...copy all the data over then in the new sheet purge the data i want to see?

This can be also the way - depends on how you filter blank data, see my 2nd question in the post #5

 

[ItalianPlatinum]

Try this instead:

Sub Test1()

  With Sheets("TREND")
    .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy Sheets("COMPARE").Range("A13")
  End With

End Sub

ok it is starting to get a little slow and its copy over the formatting of my destination

'Paste data over
  With Workbooks("Compare").Sheets("Periodic")
    .Range("A3", .Cells(.rows.count, "A").End(xlUp)).Copy Sheets("Compare").Range("A13")
    .Range("I3", .Cells(.rows.count, "I").End(xlUp)).Copy Sheets("Compare").Range("B13")
    .Range("J3", .Cells(.rows.count, "J").End(xlUp)).Copy Sheets("Compare").Range("C13")
    .Range("K3", .Cells(.rows.count, "K").End(xlUp)).Copy Sheets("Compare").Range("D13")
    .Range("B3", .Cells(.rows.count, "B").End(xlUp)).Copy Sheets("Compare").Range("E13")
    .Range("C3", .Cells(.rows.count, "C").End(xlUp)).Copy Sheets("Compare").Range("F13")
    .Range("D3", .Cells(.rows.count, "D").End(xlUp)).Copy Sheets("Compare").Range("G13")
    .Range("E3", .Cells(.rows.count, "E").End(xlUp)).Copy Sheets("Compare").Range("H13")
    .Range("F3", .Cells(.rows.count, "F").End(xlUp)).Copy Sheets("Compare").Range("I13")
    .Range("G3", .Cells(.rows.count, "G").End(xlUp)).Copy Sheets("Compare").Range("J13")
    .Range("H3", .Cells(.rows.count, "G").End(xlUp)).Copy Sheets("Compare").Range("K13")
  End With