Duplicated named ranges on sheet copy

[ITTom365]

Hello all, 

I have spent a couple of days building an excel workbook that is used by technical staff to calculate manufacturing/costing data.  The book contains a master data sheet with a number of tables and a number of named ranges (global in scope) from columns in the tables.  There is a master calculation sheet that users would copy/rename and then edit.  Data in the master data sheet tables and items in the named ranges are then used in (auto-populated) formulas to calculate outputs. 

Everything works great! except..............

Every time the master calculation sheet (or a sheet previously created from a copy) is copied, Excel duplicates the named ranges (scoped to the new sheet). 

If I could simply stop Excel creating new named ranges all would be well!! 

I have done extensive reading, and it seems this is normal behaviour?

I have seen solutions including manually deleting the new named ranges, to using vba to copy a sheet and then remove all sheet scoped named ranges, but everything seems a 'hack'. 

Is there no way for me to stop Excel creating these locally scoped ranges at each sheet copy? 

Thanks

[#2 · Accepted solution]

So solving this was relatively simple; rather than copying the sheet I copied the usedrange.
formats/formulas/tables etc all copy corretly but excel doesnt copy the named ranges.
Hopefully this is useful to someone else, as it seems to be a common query

[#3]

How is not doing a thing that is easily within Excel's design envelope a "best response"? I revive this thread because I was literally about to post the same. I have the identical setup - a clean master Worksheet gets copied in order for the copy to be instantiated/used in production.

So question once more to the Excel boffins here: Why is this happening?

I cannot find a rhyme or reason. Many of my affected names are global Lambdas. But also a couple of regular (albeit external/linked) ranges are affected.

I will probably have to write a macro to support copying the clean module. Since Excel refuses point-blank to copy my Worksheet across Workbooks, I am thinking I should write a macro that simply takes a dump (snapshot) of all names, then deletes all names, copies the Worksheet and then reinstates all the names in new Worksheet.

It is simply frustrating how many such blooming VBA macros I need to write to work around all manner of Excel bugs. (Don't get me started on Break Links.)