Hello, From “Sheet1” I want to be able to select random rows and when the macro is run columns B, C, E, G and J from the selected rows will be pasted into the “Paste” sheet. I found this code in a forum that seems to work except for they wanted Rows A, B and E.



Sub Macro1()
Dim rng As Range
For Each rng In Selection.Areas
Union(rng.Resize(, 2), rng.Resize(, 1).Offset(, 4)).Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next rng
Worksheets("Paste").Activate
End Sub

I believe I understand the “Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)” part and it works just as I need. I do not understand how “ Union(rng.Resize(, 2), rng.Resize(, 1).Offset(, 4)).” selects columns A,B and E and how to change it to get me to the results I need. Thanks


Note:
Union(rng.Resize(, 2), rng.Resize(, 1).Offset(, 4)).Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Is one line.

Attached Files

Book4A.xlsm (20.2 KB, 2 views)

This should make it a bit easier to choose which columns you want copying:This doesn't quite act in the same way as your original code which took horizontal offsets from the selection; this means that if an area's first column wasn't column A, the cells copied were offset from that column, not column A. If you want it still to operate that way, change the line:
With rng.EntireRow
to:
With rng

Note that whichever you choose above, you won't need to select ranges which cover all the columns you want to copy, only the leftmost column. Moreover, if you leave EntireRow in, it doesn't matter which column(s) you choose at all.

Thanks for the help here. Your code seems ideal for my application. The EntireRow tip was also very helpful. Thanks also for the explanation on the original code.