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?
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