Hi,

I have to copy and paste number of ranges from one spreadsheet to another for which I have written the below VBA


Private Sub CommandButton1_Click()

LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Dim R1, R2, R3, R4, R5, R6, R7, Multiplerange As Range

For i = 6 To LastRow

If Cells(i, 18) = "Open" And Cells(i, 53) = "Escalated to JLR Senior Leadership" Then

Set R1 = Range(Cells(i, 2), Cells(i, 2))
Set R2 = Range(Cells(i, 4), Cells(i, 4))
Set R3 = Range(Cells(i, 6), Cells(i, 16))
Set R4 = Range(Cells(i, 58), Cells(i, 58))
Set R5 = Range(Cells(i, 56), Cells(i, 56))
Set R6 = Range(Cells(i, 20), Cells(i, 20))
Union(R1, R2,R3,R4, R5, R6).Select
'
Selection.Copy

Workbooks.Open Filename:="C:\Documents and Settings\ssachde1\My Documents\extract.xlsx"

Dim p As Integer, q As Integer

p = Worksheets.Count

For q = 1 To p

If ActiveWorkbook.Worksheets(q).Name = "Risk Log" Then
Worksheets("Risk Log").Select
End If

Next q

erow = ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 2).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False

End If

Next i

End Sub


The entire code works fine but the only problem I have is that 'union' changes the order of selection to R1, R2,R3,R6,R5,R4.

How can keep the range in the order that I want.

Please help me.

Thanks