Pasting specific cells in A different order

[simora]

I am copying specific cells in a row to be pasted in another workbook, however, I want to post the cells in a different order than how they were copied.

This is part of the Select copy code



For i = 2 To LastRow
If Cells(i, 9) > 0 Then
Union(Cells(i, "Q"), Cells(i, "V"), Cells(i, "P"), Cells(i, "H"), Cells(i, "I")).Select


But when I'm pasting to the other sheet, I want Column I Cell Data to go into Column A
Column H to Column B
Column Q to Column C
Column V to Column D
Column P to Column E

How do I arrange this uing VBA . Excel 2003

[Kevin#]

hi Simora

You cannot use the UNION function in this manner. This function joins disparate cells together so that you can carry out a single task on all cells in that union at the same time (eg put the same formula in each cell or format each cell etc)
And there is usually no need to SELECT cells in VBA

To paste the values in a different sequence in it is necessary to copy each value separately.

Rather than "copy and paste" the code below makes the cell in sheet2 equal to the relevant cell in sheet1
You may prefer to use "copy and paste" to keep formatting the same.

The values that pass the ">0" test are copied from Sheet1 onto the next row on Sheet2 within the same worksheet i2 keeps count of the row number in sheet2.
If the sheets are in different workbooks, then the name of the workbooks need to be included when you "SET" sht1 and sht2
If the macro is going to be run multiple times, code in method to ascertain starting value for i2 , otherwise it will overwrite the previous values starting at row1

good luck
kevin



Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")


For i = 2 To lastrow

If Cells(i, 9) > 0 Then

i2 = i2 + 1
sht2.Cells(i2, "A") = sht1.Cells(i, "I")
sht2.Cells(i2, "B") = sht1.Cells(i, "H")
sht2.Cells(i2, "C") = sht1.Cells(i, "Q")
sht2.Cells(i2, "D") = sht1.Cells(i, "V")
sht2.Cells(i2, "E") = sht1.Cells(i, "P")

Else
'do nothing
End If
Next i

[simora]

Thanks Kevin (http://www.vbaexpress.com/forum/member.php?58889-Kevin):
I've used Union before but the copied area and format was always a range that was posted. Your solution is much more elegant .
Thanks for everything.