Hello,

I think that you could do this without the use of macros. If you use data connections to create a query table, you could update the sheets each week simply by refreshing the workbook. Here are the steps I took (XL 2007):

1. Data Get External Data From Other Sources From Microsoft Query
2. Select the Excel data source and click OK
3. Select the source workbook and click OK
4. If both sheets' tables show up in the list, click "Cancel" and then "Yes" to continue editing the query in Microsoft Query (if the tables don't show up, cancel and click No, then apply names to the data ranges and save to try and help the driver find the data)
5. Click "Close" and then click the SQL button to type in a SQL statement.
6. The SQL statement that I used (you'll need to change the file location and table names accordingly) was:

SELECT Table1.*
FROM `C:\Documents and Settings\Book1.xlsx`.Table1 Table1

UNION ALL

SELECT Table2.*
FROM `C:\Documents and Settings\Book1.xlsx`.Table2 Table2

7. Click OK to the message about not displaying the query graphically.
8. Click Return Data to Excel button, choose a location for your new table, and click OK.

Now, whenever your data changes, just make sure that any named ranges refer to the correct rows and refresh the workbook.

Hope this helps,

Ben