DefinedName propagation when copying a sheet

[jtyoder]

I've been searching for a fix to a problem I've had for a long time with Workbook-scoped defined names being copied to Worksheet-scoped names when I copy a worksheet. I found a reference to this problem in a topic in this forum (https://chandoo.org/forum/threads/why-do-named-ranges-propagate-when-i-copy-a-sheet-solved.10892/).

I didn't see that there was a solution back in 2013 and was wondering if Excel 2016 (which I am now working in) may have a workaround for it.

Essentially, I have numerous worksheets that reference a bunch of tables on various worksheets. I have defined Workbook-scoped named ranges for many commonly referenced values and tables so that all the other sheets can reference them and still have formulas that make sense when you look at them.

My problem is when I copy a sheet that references those named ranges by holding the Ctrl key then clicking the worksheet tab and doing a drag-drop style copy, all the Workbook-scoped named ranges that point to a table get duplicated as locally-scoped named ranges on the sheet being copied, even though the sheet being copied doesn't use those named ranges.

For instance, I have a table named tbl_risk_status listing selections for a risk status dropdown.

Risk Status
New
Active
Realized
Mitigated
Closed

I then have a named range StatusList pointing to that table.
StatusList =tbl_risk_status

The named range is then used in Data Validation as the list source since Data Validation won't allow you to use a table as a list source.


Now, if I drag-drop copy a report sheet (BWM_Oct17_Final in this example) that doesn't reference the table or named range, I get a copy of the named range that is locally scoped to the copied sheet.


Is there a way to prevent this duplication from happening?

 

[Hui]

Jtyoder

Firstly, Welcome to the Chandoo.org Forums

Have you read
https://stackoverflow.com/questions...ithout-creating-new-instances-of-named-ranges

They discuss a number of ways either around it or to recover after it

 

[Hui]

What I do is as follows

In the Name Manager you can sort by Scope by clicking on Scope or Use the Filter Dropdown
Then select a name, press Shift and select the last name, the selected Duplicate Names are highlighted in blue
Now press Delete

 

[jtyoder]

Jtyoder

Firstly, Welcome to the Chandoo.org Forums

Have you read
https://stackoverflow.com/questions...ithout-creating-new-instances-of-named-ranges

They discuss a number of ways either around it or to recover after it

Thanks for the welcome and responses!
I've been trying to avoid a VBA solution because the cell copy method doesn't include objects like buttons and other controls, charts, etc., all of which my template sheet contains. I may have to resort to this method though. The manual drag and drop copy seems to do everything I want but has the side effect of duplicating the named ranges.

As for the second option, (probably below this reply) that is the way I've been managing the problem so far but it's tedious and sometimes I forget (my lousy memory is why I like automation so much)!

 

[Chihiro]

I'd recommend using one of the method outlined in the link Hui gave you.

Edit #2 in the accepted answer would copy sheet as whole (same as manual method). Then deletes all named ranges that are scoped to copied sheet only.