VBA to replace named table, without changing table name

[axf80]

Hi,

I'm trying to use vba to update a table in my spreadsheet. Each time I run the macro it copies a table form workbook 1, called "Table1", into the same position in my new workbook. I then have some formulas in the second workbook that calculate things from the table. These formulas refer to the table name and the column headers - they need to be like this because the order of columns might change each week.

My problem is that each time the macro runs and replaces the table in workbook2 it changes the name of the table, and my formulas therefore break - all the references to Table1 get turned into REFS!.

Here's the code I use to copy and past the tables:

Sheets("workbook1").Range("Table1[#All]").Copy Sheets("workbook2").Cells(1, 1).PasteSpecial Paste:=xlPasteAll

I've tried adding the following line to rename the table back to "Table1" after pasting, but it didn't work.

Sheets("workbook2").ListObjects(1).Name = "Table1"

My formulas are like this:
=sumifs(Table1[people],Table1[Region],"",Table1[County],"")

If anyone has any suggestions or tips it would be much appreciated!
Thanks

 

[bfreescott]

There are several ways to go about this. If it were me, I would change your code so that you weren't copying Table1 from workbook1, just the data. Use currentregion or something like that to copy the actual data into workbook2 without copying it over as a valid table. If you need more help on how to select the data you want to copy let me know.