XL2007: Copying worksheets between workbooks
 

I have some reliable old code that copies worksheets between workbooks
more or less like this:

thisWorkbook.ActiveSheet.Copy after:=thatWorkbook.Worksheets(1)

When I do the copy I want to make sure I get the cell contents,
formatting, validations, any macros in the worksheet's code module,
etc. In other words I want it all.

Sadly I can't reliably count on the Copy method anymore because of the
new workbook formats with their underlying changes in row & column
counts. If thisWorkbook is an XLS and thatWorkbook is an XLSX the
Copy fails.

Obviously it can be a problem copying from an XLSX to an XLS if the
source worksheet contains more rows or columns than the target can
hold and I'm OK with that.

Can somebody suggest an alternative coding technique to replace the
Copy method?

TIA,

josh