Named Ranges on copied sheets

[markjl]

Named ranges have a context: workbook or sheet
There was a time when copying a sheet, Excel prompted me to change named range names and (I think) retain workbook scope.
Now when I copy a sheet, named ranges change from workbook to sheet context
Is there a parameter to change for Excel to prompt me with a choice?
Many Thanks
Mark

[MarvinP]

Hi Mark,

Can you have two named ranges that are the same in a workbook and on different sheets? Is that what you want?

[XOR LX]

I don't recall having the option you describe in earlier versions of Excel. Can you clarify?

This article seems to suggest that it wasn't available as far back as Excel 2000:

https://support.microsoft.com/en-us/kb/274504

Quote: "When a worksheet is copied, Excel copies any locally defined names so that they have the corresponding local definition in the new worksheet. Excel also converts any globally defined names (which cannot be defined twice globally) to local names in the new worksheet."

Regards

[markjl]

Hello

I wanted to be able to create a master sheet and then duplicate it.
When I copied a sheet with a named range I got a prompt: 'this sheet contains a named range' what do you want it to be called on the duplicate sheet ... ?

[XOR LX]

Duplicate the Master Sheet within the same workbook? Of copy it to a different workbook?

Regards

[markjl]

To duplicate a sheet in a workbook, yes and to have distinct named ranges on each sheet

[MarvinP]

Hi Mark,

It seems you are trying to do something that Excel can't do. You can't have two different named ranges, with the same name, in two different places in a workbook. You will get a "you must use unique names" error message if you try to do this. The only way to have the same name is to tie the named range to a specific sheet. Then they will be different places in Excel's structure.

[markjl]

Hello Marvin.

No I'm ok with having different names.
Take a sheet with a workbook scope name on it called A, duplicate the sheet.
You get one global one local name.
How can I get 2 global names, one called A, one called A(2)
Thanks