Paste values while maintaining table format

[bjuntz]

I am trying to copy a tab in one workbook to another. The source tab has equations and formatting that includes formatted tables, ie the table object. I want to paste just the values and formatting to the destination tab in another workbook, but I want that formatting to include the table object so that the users of the new tab have access to all the functionality of tables.

I am using Excel 2010 and want to implement this using VBA since there are multiple sheets in the source document that are copied in various combinations to destination workbooks. I have the code set to cycle though each source/destination couple, all I am missing are the couple lines to actually do the copy.

I have triedthe following by code as well as every manual paste special combination I could think of:
destRange.PasteSpecial xlPasteValues
destRange.PasteSpecial xlPasteFormats

and
sourceRange.Copy destRange
sourceRange.Copy
destRange.PasteSpecial xlPasteValues


Every time I do the PasteValues I loose the table object. I would prefer to not have to check if a listobject exists on the source sheet and copy it separably then paste values on its DataBodyRange if at all possible. There is data on each tab that is not part of the table and some source tabs do not have tables so ideally the code would handle these situations without trouble.

Thank you for any help.

 

[tygrrboi]

why not just do something like

Range2.value = Range1.value?

with range 2 being your desired location and range 1 being the values in the original table?

 

[bjuntz]

Wouldn't that be the same effect as paste values? I need the table object as well as the other formatting from the source document. This is a weekly report and range sizes change each week. The source document is set to automatically adjust formats to accommodate the changing range sizes but the destination workbooks are static copies and need the formatting copied to them.

 

[tygrrboi]

Are you trying to copy the entire sheet and paste it in another workbook with the table intact?

Why not use

Sheets("SheetNameHere").Copy After:=Workbooks("destinationworkbookname.xlsm").Sheets(Workbooks("destinationworkbookname.xlsm").Sheets.Count)

 

[bjuntz]

Yes I want to keep the table intact but I need all formulas to be changed to static values in the destination. The destination workbook template already has a tab for data to be copied to so do not want to create new tabs.