how to copy worksheet with table to new workbook... and keep table

[excel_joe]

I use the following code to copy a worksheet containing a table to a new workbook. The worksheet in the new workbook only contains the values and did not keep the table as a table.
How do I get the table to be a table in the destination? The main reason is so the 'table style' applied to the table will be available when the destination workbook is exported to PDF.
If it matters, the table is a named table populated by an xml import map. Thanks for the help.

Please Login or Register  to view this content.

[LeoTaxi]

for new workbook like this

Please Login or Register  to view this content.

kind regards
Leo

[excel_joe]

Thanks Leo,
But I see no difference in your code and mine on the copy statement. Your code also does not end up with a table on the destination worksheet. I not concerned with the full open/save Also FYI, I have found that by setting creating the destination workbook first using

Please Login or Register  to view this content.

doesn't keep the exact same page layout in print preview. I am not sure what causes this, but excel must be copying over some other settings related to pagelayout or printer setup, when I allow Excel to create the new workbook by using the copy function with no destination defined (line 1 of my code). I prefer and tried your way of creating the destination workbook first, then changed back to have excel automatically create the new workbook from the copy method.

[excel_joe]

Leo, my apologies.... After seeing no differences in the code, I decided to look deeper. By stepping through the code I found the tables were actually copied to the destination, but a later statement which I thought would only remove the formulas from all cells also caused the tables to be changed to only values (and therefore lose their alternate row style formatting). How can I remove the formulas from all the cells but keep the tables (and therefore their formatting) in place. My bad method is

Please Login or Register  to view this content.

[LeoTaxi]

Maybe like this

Please Login or Register  to view this content.

Kind regards
Leo