GURUS! Can we suspend automatic renaming of tables and ranges when replacing sheets???

Good Morning Everyone!

I have a major headache.

I have a workbook project with many references to a named sheet. And on that sheet, many references to named ranges and tables.

Periodically new information is published in the form of a worksheet.

I created a sub that would (and you more experienced folk already see it coming) rename the original worksheet to an archive sheet, rename the table and ranges (I simply put a sequential count after the original names just for future reference if need be), then:

Copy the sheet with the new information to this workbook and give it the original sheet name
Create the main table and name it the original table name
Name all ranges

In all instances above I name the items (sheet, table and ranges) the original names, so when I archive the sheet that was named "Directory" it becomes "Directory01" and the new worksheet gets named "Directory". The table "tblMain" that was on Sheets.("Directory") which became sheets.("Directory01") is renamed to tblMain01...

And the problem I face is that Excel in it's honestly incredible ways replaces ALL the references throughout my workbook to the archived names in order to keep the links valid before I get to create the new names.

I want to somehow suspend the renaming Excel is doing until I have the new sheet, table and ranges set up with the original names so all references throughout the workbook are pointing to the new information.

I am hoping (wishing, not jumping around) that there is a simple software switch, but preparing for the worst.

Thank-you VERY much.

Maybe you could make use of the INDIRECT function which allows you to build up a reference as a text string and so is insulated from automatic changes.

See Excel help for details.

Would of NEVER thought of either - looking forward to trying them out! But for right now, my head hurts...