You can do without switching from WB1 to WB2
Sub copy1()
Dim WsS As Worksheet, WsT As Worksheet
Dim SourceRng As Range
Dim PasteRng As Range
Dim Counter As Long
Dim Ccell As Range
Dim MyArray
Set WsS = Workbooks("Book1.xls").Worksheets("Sheet1") 'Needs to be open Open
Set WsT = Workbooks("Book2.xls").Worksheets("Sheet1") 'Needs to be open Open
Set SourceRng = Union(WsS.Range("D2"), WsS.Range("E2"), WsS.Range("G2"))
Set PasteRng = Union(WsT.Range("K1"), WsT.Range("L1"), WsT.Range("G1"))
ReDim MyArray(1 To SourceRng.Cells.Count)
Counter = 1
For Each Ccell In SourceRng
MyArray(Counter) = Ccell
Counter = Counter + 1
Next
Counter = 1
For Each Ccell In PasteRng
Ccell = MyArray(Counter)
Counter = Counter + 1
Next
End Sub
Bookmarks