Copy and Pasting Loop

[Lyrad39]

Good day Community,

I am intending to loop and copy the tables (K4:T20) as well as other tables on the right till the end, pasting them below row 20 and so on. However, I am stuck with the loop code and I hope to seek your help with it. Attached photo as shown for example.

These are the codes gotten with recording but I am unable to figure it out:

Sub Macro4()
'
' Macro4 Macro
'

'
    Range("K4:T20").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=3
    Range("A21").Select
    ActiveSheet.Paste
    Range("U4:AD20").Select
    Selection.Cut
    ActiveWindow.SmallScroll Down:=15
    Range("A38").Select
    ActiveSheet.Paste
End Sub

 

[Lyrad39]

Attached as shown.

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Does this do what you want? If not, more details please.
Test with a copy of your workbook.

Sub MoveTables()
  Dim Lastcol As Long, c As Long
  
  Lastcol = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  For c = 11 To Lastcol Step 10
    Cells(4, c).Resize(17, 10).Cut Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
  Next c
End Sub

 

[Lyrad39]

Thank you for the help Peter, appreciate it! All seems good except the last coloumn of results for Sd was shifting down to the left on column A instead and it was shifted down by one table starting from A19.

 

[Peter_SSs]

Can't tell anything from the picture. Can you post the original sample data with XL2BB so that I can copy it to test with?

 

[Lyrad39]

My apologies Peter but my workplace does not allow installation of add-ins. I have attached the proper screenshots of before VS after. Thank you so much for the help.

 

[Peter_SSs]

Not following the images. Original sample data (post 2 image) had that first #REF! in cell A4. My code was based on that and left the first table where it was and moved moved all the other tables below that first one on the same sheet.
Your 'before' image now still shows that first #REF! in cell A4. Your 'after' image shows that first #REF! in cell B3. Have you altered the code in some way?

 

[Lyrad39]

Ohh yes I did some adjustments, I have revert it and the codes work now awesome!! Would you know how I could delete the rows for "John David Saly...." and just paste the results?

 

[Peter_SSs]

Would you know how I could delete the rows for "John David Saly...." and just paste the results?

Try

Sub MoveTables_v2()
  Dim Lastcol As Long, c As Long
  
  Lastcol = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  For c = 11 To Lastcol Step 10
    Cells(5, c).Resize(16, 10).Cut Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
  Next c
  ActiveSheet.UsedRange.Offset(, 10).ClearContents
End Sub

 

[Lyrad39]

It works!!! Thank you so much Peter!