Paste selected range of cells in other worksheets first free cell

[Jani_Jani]

Hello,
I know there are few similar threads and I've tried a few advised codes, but it still doesn't work at all. I've also tried to change them a bit to match my purpose but I failed (call me dummy, but I've spend the whole weekend working on this thing and my brain fails me).

What I need is a VBA code which would let me:

1. From sheet "Africa" copy cells from between D1 and K1 down to the last filled row.
2. Paste them in A1 of sheet "World"
3. From sheet "Asia" copy the cells from between D2 and K2 down to the last filled row.
4. Paste them in the first empty cell of sheet "World" under the data copied form "Africa"

Anything which works is fine but the simpler the better as my thinker is fried already ...
Thank you.

 

[Fluff]

How about

Sub CopyShts()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("World")
   With Sheets("Africa")
      .Range("D1", .Range("K" & Rows.Count).End(xlUp)).Copy Ws.Range("A1")
   End With
   With Sheets("Asia")
      .Range("D2", .Range("K" & Rows.Count).End(xlUp)).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
   End With
End Sub

 

[Jani_Jani]

How about

Sub CopyShts()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("World")
   With Sheets("Africa")
      .Range("D1", .Range("K" & Rows.Count).End(xlUp)).Copy Ws.Range("A1")
   End With
   With Sheets("Asia")
      .Range("D2", .Range("K" & Rows.Count).End(xlUp)).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
   End With
End Sub

It's... It's... It's ALIVE !
Thank you so much!

I've adapted to code to work also in other similar cases I have a problem with one instance though where I have to paste the values (as everywhere else the same columns from different sheets one under the other) but with source with Paste:=xlPasteAllUsingSourceTheme, not sure in which place this command should go as to not spoil everything...

 

[Fluff]

Try

Sub CopyShts()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("week")
   With Sheets("pcode")
      .Range("D1", .Range("K" & Rows.Count).End(xlUp)).Copy
      Ws.Range("A1").PasteSpecial xlPasteValues
      Ws.Range("A1").PasteSpecial xlPasteFormats
   End With
   With Sheets("Asia")
      .Range("D2", .Range("K" & Rows.Count).End(xlUp)).Copy
      Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats
   End With
End Sub

 

[Jani_Jani]

Try

Sub CopyShts()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("week")
   With Sheets("pcode")
      .Range("D1", .Range("K" & Rows.Count).End(xlUp)).Copy
      Ws.Range("A1").PasteSpecial xlPasteValues
      Ws.Range("A1").PasteSpecial xlPasteFormats
   End With
   With Sheets("Asia")
      .Range("D2", .Range("K" & Rows.Count).End(xlUp)).Copy
      Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats
   End With
End Sub

Today you've became someones hero

 

[Fluff]

Glad to help & thanks for the feedback