How do I apply globally defined names to newly inserted templates?

[Manzomanen]

Hi,

I have got a problem: I have got a workbook with several sheets and globally defined names (scope = workbook). Further, I have extracted two templates out of that workbook (type-format .xltm) that contain locally defined names. These templates carry references to those names defined in the workbook. Now, I want to insert the templates several times, using VBA. It works.
However, after the insert, all the globally defined names exist in each inserted sheet as locally defined names, too, which leads to conflicts. I don't mean that! Unfortunately, these now locally defined names might carry outdated references (because the range of the original globally defined name in the workbook has changed, e.g.) and as the scope of the local ones is smaller they will be preferred by Excel.
I tried to solve this problem by deleting all globally defined names in the templates. Now, after the insert, the globally defined names exist only once and without any conflict. And: the inserted sheets partly recognize those global names.
What does "partly" mean? When I have a look at the dropdown menu on the left hand side of the "fx" field (between the menus and the actual sheet), I see all the global names. However, none of the cells that contain references to these names work correctly. They all show a #NAME error. The "funny" thing is: I can fix that very easily by hand. All I had to do is to select each cell with a formula that references one of the globally defined names, select this formula and then press enter. If I do that, the error is gone and the cells work correctly. However, this is obviously no option for me: to select every cell manually just to press enter. I would like to advise the newly inserted templates to automatically accept the existing names.

EDIT: I have tried Application.Calculate - didn't change anything.

Could you help me? Any other solution strategies? Thanks in advance :-)

I am using Excel 2010 and Win 7.

All the best - Manzomanen

[Jacc]

Interesting problem. I have no solution right now, I am just about to leave. If you are working with this manually as well I can recommend this improved Name Manager. It can convert globals to locals and so on.
http://www.jkp-ads.com/officemarketplacenm-en.asp

[Manzomanen]

Thanks Jacc, I will try the tool out next week, I'm about to leave, too :-)

However, if somebody could still provide me an automated solution I would appreciate it.

[Manzomanen]

I have now tried out your recommended tool. And it helped me, thanks! When I had all the duplicate names (global & local ones, i.e. before deleting all globally defined names in my templates), I could delete the "wrong" duplicates (i.e., the local ones) with the tool and instead, the inserted sheets recognized the globally defined names only.

Now I have written the following code to do this work automatically:

Please Login or Register  to view this content.

However, I didn't find a way (didn't further look for it, though), to "activate" the globally defined names in my newly inserted templates if I had deleted those global names from my templates before.