Modifying Named Ranges in Name Manager in field 'Refers To'

[dentler]

I am trying to find a way to simplify modifying Named ranges in Name Manager.
I can do this with this code:

Please Login or Register  to view this content.

Where I have a problem is that I have up to a dozen different ranges that I want to modify at different times, and rather than writing code for each Named Range, I would like to place the range info in another sheet for all named ranges, so that I can pull the desired range and swap it into the sub RedefinedNamedRange. The issue I am having is that if I place them on another sheet in a cell code such as

Please Login or Register  to view this content.

and then try to reference it, it will change the Named range, but it places double quotation marks, making the range ineffective.

So instead of the Named range Refers To being

Please Login or Register  to view this content.

, I end up with this:

Please Login or Register  to view this content.

where the additional quotations occur rendering the code ineffective. I have been unsuccessful in finding a way to remove the quotation marks when trying to pull the code from another sheet.

Does anyone have any ideas or solutions?

[Glenn Kennedy]

I know nothing about VBA, but looking at this, my guess is that your output needs to be either this (more likely - this is what formuale need)

='Sheet1'!$E$2:$E$41

or this (less likely)

="'Sheet1'!$E$2:$E$41"

Give it a go... but... do note, I know nothing about VBA....

[dentler]

Actually, I just solved my problem, or at least I figured out how to handle it and will need more coding to handle multiple name ranges. To begin with completely ignore the code in the original post. I do not need it.

In the Name Manager, I changed the Refers To to

Please Login or Register  to view this content.

Then on sheet1 in cells 254B, C, and D, I placed the following strings:

Please Login or Register  to view this content.

Then I created the following sub routine

Please Login or Register  to view this content.

So I can change the value for the named range in Name Manager on the fly with the simple code above.
This is the main problem I was trying to resolve. Now I have more code to right, since by the time I am through, I will have approximately 156 code lines to index along with 156 INDIRECT entries in Name Manager. This should be fun!

*** Note: In my code, I refer to sheet1 as code