Nov 14 2022 12:27 AM
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
Nov 14 2022 04:06 AM
SolutionApr 26 2023 09:32 AM
Dec 31 2023 06:26 PM
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?
Jan 01 2024 06:40 AM
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.
Jan 05 2024 12:56 AM
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.
Nov 14 2022 04:06 AM
Solution