VBA Append Tables - Unexpected result

[oddzac]

Hi all!
Got another newb question for ya.

I'm attempting to grab updated data from a query table and pin it to the bottom of a static table to track year to date trends.

Here's the code I've got:

Sub TC_Copy_Paste()
    Dim TC As Worksheet
    
    Set TC = Worksheets("TC Data Dump")

    With TC
              
        .Range("P3", ("X3" & .Range("P" & Rows.Count).End(xlUp).Row)).Copy Destination:=TC.Cells(Rows.Count, 5).End(xlUp).Offset(1)
        .Range("AJ3", ("AR3" & .Range("AJ" & Rows.Count).End(xlUp).Row)).Copy Destination:=TC.Cells(Rows.Count, 26).End(xlUp).Offset(1)
        
    End With
End Sub

The code is doing the same thing to 2 separate tables and it runs fine *but* the macro adds an additional 20 blank rows to the bottom of each table.


Ex.
Before:

After:

Thank you in advance!

 

[Anthony47]

If those in P3 and AJ3 are "Tables" then try this version:

Sub TC_Copy_Paste()
Dim TC As Worksheet, RowNum As Long
'
Set TC = Worksheets("TC Data Dump")

With TC.Range("A2").ListObject
    RowNum = Application.WorksheetFunction.CountA(.ListColumns(1).DataBodyRange)
    .DataBodyRange.Cells(1, 1).Resize(RowNum, 9).Copy Destination:=TC.Cells(Rows.Count, 5).End(xlUp).Offset(1)
End With

With TC.Range("AJ3").ListObject
    RowNum = Application.WorksheetFunction.CountA(.ListColumns(1).DataBodyRange)
    .DataBodyRange.Cells(1, 1).Resize(RowNum, 9).Copy Destination:=TC.Cells(Rows.Count, 26).End(xlUp).Offset(1)
End With
End Sub

 

[oddzac]

If those in P3 and AJ3 are "Tables" then try this version:

Sub TC_Copy_Paste()
Dim TC As Worksheet, RowNum As Long
'
Set TC = Worksheets("TC Data Dump")

With TC.Range("A2").ListObject
    RowNum = Application.WorksheetFunction.CountA(.ListColumns(1).DataBodyRange)
    .DataBodyRange.Cells(1, 1).Resize(RowNum, 9).Copy Destination:=TC.Cells(Rows.Count, 5).End(xlUp).Offset(1)
End With

With TC.Range("AJ3").ListObject
    RowNum = Application.WorksheetFunction.CountA(.ListColumns(1).DataBodyRange)
    .DataBodyRange.Cells(1, 1).Resize(RowNum, 9).Copy Destination:=TC.Cells(Rows.Count, 26).End(xlUp).Offset(1)
End With
End Sub

This does the trick! Would it be too much to ask you *how*, though?

I just stepped into VBA over the weekend and I'm trying to learn via these forums and the microsoft site, but it's been a little.. dense.

 

[Anthony47]

A "Table" is a ListObject, with certain specific properties and Method.
I used .ListColumns(1).DataBodyRange (ie column 1 of the table "data" range) and WorksheetFunction.CountA to count how many rows are in that table; with this information we can properly determine wich range has to be copied

You might look at this page to learn about Tables: Excel: Working with Tables (VBA)