Copy data from one table to another - hidden columns

[Steven101]

Hi I have 2 sheets;

Sheet1 has Table1
Sheet2 has Table2

Both tables have identical headings, however both have multiple columns (same) hidden to make ease of viewing

I'm looking to create a macro that will copy the table contents of table1 into the last completed row +1 of Table2. Essentially adding to Table2.

Any help would be appreciated.

Thank

 

[John_w]

Try this:

Public Sub Copy_Table1_To_Table2()
    
    Dim table1 As ListObject
    Dim table2 As ListObject
    Dim table2numDataRows As Long
    
    Set table1 = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    Set table2 = ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table2")
    
    With table2
        table2numDataRows = .DataBodyRange.Rows.Count
        .Resize .Range.Resize(.Range.Rows.Count + table1.DataBodyRange.Rows.Count)
        table1.DataBodyRange.Copy .DataBodyRange.Offset(table2numDataRows, 0)
    End With
    
End Sub

 

[Steven101]

Thanks John_w, works a treat, with one problem.

The data copied over includes formula, is there a way of pasting the data in as values?

Thanks, S

 

[John_w]

This macro copies Table1 values:

Public Sub Copy_Table1_Values_To_Table2()
    
    Dim table1 As ListObject
    Dim table2 As ListObject
    
    Set table1 = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    Set table2 = ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table2")
    
    With table2
        .DataBodyRange.Resize(table1.DataBodyRange.Rows.Count).Offset(.DataBodyRange.Rows.Count, 0).Value = table1.DataBodyRange.Value
    End With
    
End Sub

The original code can be simplified:

Public Sub Copy_Table1_To_Table2()
    
    Dim table1 As ListObject
    Dim table2 As ListObject
    
    Set table1 = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    Set table2 = ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table2")
    
    With table2
        table1.DataBodyRange.Copy .DataBodyRange.Resize(table1.DataBodyRange.Rows.Count).Offset(.DataBodyRange.Rows.Count, 0)
    End With
    
End Sub