have multiple tables on separate sheets, how to combine

[dmcgov]

i have this routine that is not working on tables. How can I copy tables from multiple sheets and have them combine into one?

this is not working:

Please Login or Register  to view this content.

the problem is that this copies only the text and not the structure of the table (like banded rows, header row with drop downs, etc)

so i did some research and found this link

i expected this snippet of code to work, but i get an error that the subscript is out of range. i went to the "Purchase Requisitions" sheet and verified the name of the table so why isn't this working"

Please Login or Register  to view this content.

the sheets that have the tables are: "Purchase Requsitions", "Outstanding POs", "Open POs", "Closed POs" and "Archived POs". All the tables go to the "Summary" sheet.

[CK76]

Can you upload sample file with small amount of data?

Normally what you'd need to do is, to copy databodyrange of each table. Paste as value. Convert entire range into new table range.

You can find where I did something like above in thread below (post #13 is where convert range to table is done)
https://www.excelforum.com/excel-pro...one-sheet.html

[dmcgov]

so i read your post and the link. so what i have that is working so far is this:

Please Login or Register  to view this content.

The only issue is that the tables are right below each other and i would like a blank line in between. how can i do that?

[CK76]

So you are actually not combining multiple table into one table, I take it?

But copying tables that reside in separate sheet into single sheet but as individual table?
If you are copying table as is, I'd recommend just copying entire .ListObject and pasting. Instead of copying range and pasting as value.

At any rate, with your code try, Rows.Count + 1 instead of just Rows.Count

[dmcgov]

so here is what i tried (got an error)

Please Login or Register  to view this content.

when i ran it, i got a "Run Time Error 1004: Application-defined or object-defined error". what did i do wrong>?

[CK76]

Can you upload a sample workbook with sanitized data? It'll help in optimizing your code.

[dmcgov]

sorry it took so long to do this, but here it is

[CK76]

Ah, my bad on Rows.Count + 1. I forgot that that portion counts entire worksheet row range.

You should use .Offset(1).PasteSpecial

However, what are you trying to copy to "Summary" sheet from "Outstanding POs"? Only partial filtered data or entire table?

Below code is to copy entire table.

Please Login or Register  to view this content.

[dmcgov]

Thanks CK76, that did the trick. i put the rest of the sheets in the code as well. i like it that it keeps the format of the table (like date and dollars).

here is the final code:

Please Login or Register  to view this content.