Past Entire Table to Last Row

[new11]

Hello all,

I hope everyone's enjoying this sunny weather (if the suns shining)
Just wondering if I might be able to receive some help, please.

What I'm trying to achieve is copying an entire table, minus headings and past all the data into the last row of a new table, in a new sheet. I've been able to copy the table but the headings are also copied. Additionally when pasting I'm only able to past 1 row. :/
Sorry in advance if it's anything obvious. I'm a complete novice. :}

Many thanks in advance for any assistance and feedback.

Dim rng As Long
Dim LastRow As Long
Set rng = ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table2").Range
    LastRow = rng.Find(What:="*", _
    After:=rng.Cells(1), _
       Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = Sheet1.ListObjects("Table1").Range

 

[Michael M]

Maybe this way....

Sub MM1()
Dim lr As Long
lr = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row 'Change sheetname to suit
Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count).Copy _
Sheets("Sheet3").Range("A" & lr + 1) 'change sheetname and cell address to suit
End Sub

 

[Alex Blakenburg]

Not as compact as Michael's but looks a bit more like the way you were trying to do it and should run without modification.
DataBodyRange gives you the Table rows without the heading row.

Sub AppendTable1ToTable2()

    Dim DestDataRowCnt As Long
    Dim DestLastRow As Long
    Dim DestRng As Range
    Dim SrcRows As Long
    Dim SrcCols As Long
    
    SrcRows = Sheet1.ListObjects("Table1").DataBodyRange.Rows.Count
    SrcCols = Sheet1.ListObjects("Table1").DataBodyRange.Columns.Count
        
    DestDataRowCnt = Sheet2.ListObjects("Table2").DataBodyRange.Rows.Count
    
    Set DestRng = Sheet2.ListObjects("Table2").ListRows(DestDataRowCnt).Range.Cells(1)
    Set DestRng = DestRng.Offset(1, 0).Resize(SrcRows, SrcCols)
    
    DestRng = Sheet1.ListObjects("Table1").DataBodyRange.Value

End Sub

 

[new11]

Not as compact as Michael's but looks a bit more like the way you were trying to do it and should run without modification.
DataBodyRange gives you the Table rows without the heading row.

Sub AppendTable1ToTable2()

    Dim DestDataRowCnt As Long
    Dim DestLastRow As Long
    Dim DestRng As Range
    Dim SrcRows As Long
    Dim SrcCols As Long
   
    SrcRows = Sheet1.ListObjects("Table1").DataBodyRange.Rows.Count
    SrcCols = Sheet1.ListObjects("Table1").DataBodyRange.Columns.Count
       
    DestDataRowCnt = Sheet2.ListObjects("Table2").DataBodyRange.Rows.Count
   
    Set DestRng = Sheet2.ListObjects("Table2").ListRows(DestDataRowCnt).Range.Cells(1)
    Set DestRng = DestRng.Offset(1, 0).Resize(SrcRows, SrcCols)
   
    DestRng = Sheet1.ListObjects("Table1").DataBodyRange.Value

End Sub

Hi @Alex Blakenburg

You're an absolute marvel! That was exactly what I wishing to do, lovely and simple too! Once again the incredible users of this forum have save my sanity.

@Michael M Thanks for your post too. I was playing around with it, but couldn't quite get it working for some of my editions.

Many thanks again everything & stay happy and healthy!

 

[Alex Blakenburg]

Thank you for the feedback. We were glad we could help.