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.)

[#4]

@ITTom365 

I have the same problem with copying sheets, but I don’t think that your solution works in my case. I use a lot of groups on some sheets. I don’t know of any way to Include the groups in the copy except by the Move or Copy option on the sheets tab. Do you have a way to include groups?

[#5]

@ecovonrein 

I would respectfully suggest that rather than fighting Excel it is better to exploit what it offers.

If you wish to use a sheet as a template to be copied within the workbook do not use that sheet to hold data.  Its sole purpose is to initialise copies with sheet-local names and with any appropriate formatting.  Do not copy such a sheet  between workbooks or you will create inter-workbook links.

If you prefer to use a 'January' sheet (say) as the template for 'February' etc, avoid any defined names that are scoped to the workbook, use only sheet-scoped names.

The named Lambda functions should not duplicate when sheets are copied within a workbook but, if copied to a different workbook, should create a usable function scoped to the receiving workbook with no references to the source.

Something that may prove useful is to use workbook scoped names for 3D ranges, for example

='January 24:February 24'!$E$5:$E$75

 These work well with dynamic array functions such as TOCOL and VSTACK.

[#6]

Should being the operative term, Peter. It does not work. Without rhyme or reason, certain local names end up in the global scope, and vice versa. It is wild, particularly since the first operation invariably creates #REF! everywhere as the local range references of course fail to resolve in the global scope.

I need to trawl thru the Name Manager each time I instantiate a new Worksheet by copy.