We do not always control what spreadsheets we have to work with, although I try!
I need to go through one spreadsheet and find row meeting certain criteria and then select/copy various cells from that sheet and paste them into another workbook. I have done this successfully when the cells I am copying are 'linear' or 'progressive' (not sure what work is appropriate). i.e.
Range("AI" & x & ", D" & x & ", E" & x & ", F" & x & ", Q" & x & ", R" & x & ", S" & x).Select
Selection.Copy
[switch to another open workbook/sheet]
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
But the spreadsheet I am pasting the values into is set up with the columns in a different order. Here is what I was thinking, although it doesn't work.
Range("I" & x & ", D" & x & ", C" & x & ", E" & x & ", Q" & x & ", A" & x & ", H" & x).Select
Selection.Copy
[switch to another open workbook/sheet]
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Then I stumbled across UNION and that would help but still ended up with the data in the original order.
Set u1 = Range("A" & x)
Set u2 = Range("C" & x)
Set u3 = Range("D" & x)
Set u4 = Range("E" & x)
Set u5 = Range("H" & x)
Set u6 = Range("I" & x)
Set u7 = Range("Q" & x)
Union(u6, u3, u2, u4, u7, u1, u5).Copy Workbooks("second_workbook.xls").Sheets(bSheet).Range("A12")
Any suggestions/guidance is appreciated.
Bookmarks