Issue: combining multiple workbooks with same table names

[spydey]

I have a template (*.xltm) which has a few different tables in it, each table is named based upon what it is used for.

Example: tblDates; tblLocations; tblStatus; etc

A number of in-cell formulas reference the tables by name and their contents.

When I save the file, it gives it a unique file name. I then go back and use the template to generate a different file; etc etc.

I then combine all of the different workbooks into a single workbook, so that I have all of the different files in one single workbook.

However, this has caused a problem. Due to all of the files having the same tables and the tables all named the same, when copied over to the same workbook, the table names are being changed automatically.

What used to be tblDates is now tblDates2, tblDates3 .... tblDates432, etc.

This is happening to all of my tables.

The issue is that the incell formulas are now referencing tables not pertaining to that specific worksheet's tables.

I have no idea how to stop or correct this. Normally I can think of something to circumvent the issue, but nothing is coming to mind.

Any ideas on how to avoid this issue?

-Spydey

 

[spydey]

I had a thought: my worksheets are unique names, numerical. Example 14586. There are no duplicate worksheets when I combine them all into a single workbook.

What if I had some code to that automatically added the sheet name (unique number) to the end of every table in the worksheet when it is initially generated from the template?

I would also have to update my formulas to reference the table name correctly too .......

So basically, I input a number into cell B2. I have some code that upon changes to the worksheet, captures that number and renames the worksheet to that number.

I would need some code that does the same thing but renames the tables to include that number at the end, and then the incell formulas would need to reference the same tables. So basically my table names would be "dynamic" and my incell formulas would be "dynamic" too, so that if I change the number in B2, everything changes accordingly and correctly.

Would this automatically update any Data Validation lists I have sourcing from those same tables?

-Spydey