Copy values multiple columns (non-adjacent) to another sheet

[duranimal86]

I have a workbook with 2 sheets that are identical, except one of them has a data connection that can be refreshed. I want to be refresh the data and then run this code to automatically copy those columns (as values) to the other sheet. I tried to combine into a single operation, but it wouldn't work with more than 2 column references. The code below works, but it is slow and i know there are much better options.

Sub CopyValues()

Sheet3.Range("F:G").Value = Sheet2.Range("F:G").Value
Sheet3.Range("L:M").Value = Sheet2.Range("L:M").Value
Sheet3.Range("O:P").Value = Sheet2.Range("O:P").Value
Sheet3.Range("S:T").Value = Sheet2.Range("S:T").Value

End Sub

 

[Z51]

Rather than using entire columns, can you find the last used row and use that to narrow the value assignments? For example, if column F always has a value in it to the very bottom row:

Sub CopyValues()
Dim lastrow as Long
lastrow = Sheet2.Range("F" & Rows.Count).End(xlUp).Row

Sheet3.Range("F1").Resize(lastrow, 2).Value = Sheet2.Range("F1").Resize(lastrow, 2).Value
Sheet3.Range("L1").Resize(lastrow, 2).Value = Sheet2.Range("L1").Resize(lastrow, 2).Value
Sheet3.Range("O1").Resize(lastrow, 2).Value = Sheet2.Range("O1").Resize(lastrow, 2).Value
Sheet3.Range("S1").Resize(lastrow, 2).Value = Sheet2.Range("S1").Resize(lastrow, 2).Value

End Sub

 

[duranimal86]

No, unfortunately it isn't a clean data table, so there are blank rows in the middle of what i need to copy.

 

[Z51]

Blank rows in the middle isn't an issue. You just need to determine what is the absolute last used row on the original sheet (Sheet2). If that's column A or ZZ or whatever, use that column in the code to determine the last row. Otherwise you could just use something like the following to get the very last row # of that worksheet's used range:

lastrow = Sheet2.Range("A1").SpecialCells(xlLastCell).Row

Then you wouldn't need to adjust anything else in the code.

 

[duranimal86]

Oh, ok. I tested your code and it worked and was A LOT faster than my original code. Thank you!