Why can't i change scope of named range within name manager?

You have to delete the old name and recreate it with the required new scope.

No, not directly. If I remember correctly, the Name Manager tool from Jan Karel Pieterse/Charles Williams does allow that, though I suspect it simply does the delete and recreate for you.

I did some searching on this subject a couple of years ago because I needed to change some names on a file at work where no add-ins are possible. I found some code which worked for me - but please check it on a copy of your file before using it on your actual file. It should change all names which are only worksheet-scope to workbook-scope.

Please Login or Register  to view this content.

Hope that helps.

Edit: note that there's no error checking in this macro. If there are two worksheet-scope Names with the same name, I don't know what the result will be.

Just to point out this is intentional by MS.

Named ranges have to have unique names, within their scope. This means you can have a named range called "MyRange" with a scope of Workbook and another named range with the same name with a scope of Sheet1, but you cannot have 2x workbook scope with the same name or 2x Sheet1 with the same name.

Ok, easy enough Excel should just check if the name you try and rename it to already exists. Sure they could but...

The scope of a named range would likely impact the refers to as well, at least in terms of what you would want it to refer to. Excel cant really check if it refers to what you want it to.

Lastly, copy/paste. Its possible to duplicate named ranges without even knowing you have using copy/paste. I believe the scope changes when this happens (at least for workbook scope named ranges, they get copied as sheet level) to prevent them from completely breaking.

With all the above allowing people to rename could be a mess. As pointed out, either recreating them or using a macro/3rd party solution should work.

Originally Posted by Zer0Cool

...Excel should just check if the name you try and rename it to already exists...

I should point out that the macro I posted above doesn't have any error-checking in it and I don't know what will happen if you have two worksheet-names with the same name. So, as I implied above, test on a back-up copy first!

Hi, I am new to the forum. Thank you for the macro! I installed the macro in my Excel Personal file and ran it. I did not get an error message but the scopes of my formula names were unchanged. What am I missing? Is there a requirement that the macro run from the workbook that has the issue? Any help you can offer is appreciated. Thank you

Hi ericgpe,
Yes, you need to run the macro from the workbook which you want to change. To do this, open the Visual Basic editor (press Alt-F11), make sure you select the correct workbook on the left-hand side, insert a new module, copy/paste the code, then run it. After it's run, you can save the workbook as a normal .xlsx file and the macro will be deleted (you'll get a warning dialogue box about this) or save it as a macro-enabled .xlsm file and it'll be kept.
Hope that helps.
AS