SOLVED

Duplicated named ranges on sheet copy

Occasional Contributor

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

 

1 Reply
best response confirmed by ITTom365 (Occasional Contributor)
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