Forum Discussion
Duplicated named ranges on 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
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
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?
- 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.)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$75These work well with dynamic array functions such as TOCOL and VSTACK.
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.
