Give user option to NOT take selected/all Defined Names to new workbook when copying worksheet
If you copy a worksheet from one workbook to another, Excel currently also automatically copies ALL Defined Names (even if there is no range in the source workbook to which these refer!). It will also copy without warning names with a different scope to an existing name in the target workbook (i.e. If an existing name has Workbook scope, a copied Sheet scoped name will be copied without an warning of the duplication!). This can result in a huge number of redundant or conflicting Names in the target workbook.
Thanks for the suggestion Col. We’ll prioritize taking a look at this according to how many votes it gets, so please keep voting for the things you like most!
John [MS XL]
Col Delane commented
The issue I raised is NOT about how to deal with duplicate Defined Names in the source and destination workbooks, but about the default (and unchangeable) process that ALL Defined Names from the source workbook, whether used by or relevant to the sheet being copied or not, are copied across to the destination workbook, thus in most cases resulting in redundant external links and a whole lot of unnecessary "noise" in the destination workbook.
Finn Haverkamp commented
Agreed. Names need work. I tend to not use them. So if I'm using another excel file as a template for something I need to work on, I always to go Data > Connections, and remove all links. And to Formulas > Name Manager, and delete all names. One thing you could try is to copy the worksheet to a new workbook, then delete all names, then copy that sheet to the book you actually want to move it to.
It also generates one modal dialog per Name, which is a pain.