Past Entire Table to Last Row

new11

New Member
Joined
Sep 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,876
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this way....
VBA Code:
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,654
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

new11

New Member
Joined
Sep 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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!
 

Forum statistics

Threads
1,140,928
Messages
5,703,220
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top