Copying 2 dynamic ranges together

[CookieMonster76]

Hi
I have this bit of code, but is there a way to combine it so it copies A1:C and P1:R at the same time - By doing it the way I am currently I am having difficulty in lining it up on the destination spreadsheet due to the fact that there are blanks in Col D in Sheet 3, so the previous last cells in Col A and Col D are not the same

Sheets("Sheet3").Select

With (Sheets ("Sheet2")
.Range("A1:C" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Sheet3").Range("A1048576").End(xlUp).Offset(1, 0)
.Range("P1:R" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
End with

Range("D1048576").End(xlUp).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Thanks

Paul

 

[Fluff]

If you want cols P:R copied as values try

Dim NxtRw As Long
Sheets("Sheet3").Select

With Sheets("Sheet2")
NxtRw = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Range("A1:C" & .Range("A" & .Rows.Count).End(xlUp).Row).copy Destination:=Sheets("Sheet3").Range("A" & NxtRw)
.Range("P1:R" & .Range("A" & .Rows.Count).End(xlUp).Row).copy
End With

Range("D" & NxtRw).End(xlUp).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

 

[CookieMonster76]

Thanks for this

However, I get a 1004 run-time error at this point:

.Range("A1:C" & .Range("A" & .Rows.Count).End(xlUp).Row).copy Destination:=Sheets("Sheet3").Range("A" & NxtRw)

 

[Fluff]

Apologies it should be

NxtRw = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)[COLOR=#ff0000].Row[/COLOR]

 

[CookieMonster76]

Thank you so much, you're a godsend

 

[Fluff]

Glad to help & thanks for the feedback