How do I Append Multiple Rows from one Excel Table (ListObject) to Another?

Question

I have two tables:

    Table_1         Table_2
   A   B   C       A   B   C
 -------------   -------------
1| A1| B1| C1|  1| A2| B2| C2|
2| A1| B1| C1|  2| A2| B2| C2|
3| A1| B1| C1|  3| A2| B2| C2|

Resulting table:

    Table_1   
   A   B   C  
 -------------
1| A1| B1| C1|
2| A1| B1| C1|
3| A1| B1| C1|
4| A2| B2| C2|
5| A2| B2| C2|
6| A2| B2| C2|

Table_2 is a temporary table (ListObject) that queries a database for entries using data connection.

Table_1 is a table (ListObject) that acts as a collective list of entries. It is kept separate because it (1) shortens the query time in Table_2 and (2) has some programmatic edits.

I have VBA code that copies Table_2 to Table_1 and then updates the connection string for Table_2 to not include entries with dates within the range of Table_1. The result is that Table_2 only pulls new data.

My code (correctly) copies data from Table_2 to Table_1:

For Each temprow in Table_2.ListRows
  Set newRow = table_1.ListRows.Add
  tempRow.Range.Copy
  newRow.Range.PasteSpecial xlPasteValues
Next

This works great if Table_2 (new entries) only has a dozen entries. Occasionally, Table_2 will have a couple hundred entries, which will literally take 20 minutes to complete. I suspect it's because I'm doing a couple hundred iterations of .Copy and .Paste.

Is there a way I can do it wholesale: copy ALL of Table_2 and just make it part of Table_1? I feel like what I want to do should only take 1 second to execute programmatically, not 20 minutes. I have no conditions or exceptions. I want EVERYTHING from Table_2, which should make it easy. I'm probably going about it the wrong way. Any help is appreciated. Thanks.

Whoredom

Perhaps you can copy the .DataBodyRange in a single step. – Highoctane

Ron Rosenfield's response above worked for me AFTER I removed the totals rows. Maybe that's why Schmalls couldn't get it to work. I would add this as a comment, but I don't have the reputation. – Territorial

Answer 1

Perhaps this, changing the worksheet appropriately:

Option Explicit
Sub CombineTables()
    Dim LO1 As ListObject, LO2 As ListObject

With Sheet3
    Set LO1 = .ListObjects("Table_1")
    Set LO2 = .ListObjects("Table_2")
End With

LO2.DataBodyRange.Copy Destination:= _
    LO1.DataBodyRange.Offset(LO1.DataBodyRange.Rows.Count).Resize(1, 1)

End Sub

Highoctane

This sent me in the correct direction. It however did not append the rows to the table, it just pasted them after the table. I had to add the step of creating a new table row at the bottom of the table and using that as the destination for my copy. For example, the last line would be replaced with: LO2.DataBodyRange.Copy Destination:=LO1.DataBodyRange.ListRows.Add.Resize(1, 1) – Copperas

@Copperas It works properly without those changes here (Excel 2007), so I'm guessing there's something different about either your environment or your data. – Highoctane

That is weird that I had that output then. I guess mine can just be an alternate version if anyone runs into the same issue. – Copperas

How could I modify this if the destination table is in another sheet? – Saratov

Answer 2

Try using the SQL union statement for fast table joining (ODBC):

SELECT * FROM [Sheet1$] UNION SELECT * FROM [Sheet2$]

where Table 1 is on a sheet named "Sheet1" and Table 2 on "Sheet2".

Do this by going to "Data->From other sources->From Microsoft Query"

Coinsurance

Answer 3

Copy the Source table and Special paste Values + formating in temp range

• Cut the temp range
• Add a last row to the Destination Table
• Select the Last Row
• Insert the Cut selection into the Destination Table with Shift:=xlDown
• Delete the last row it you don't want it

Exocarp