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

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

Sub RedefineNamedRange()
Dim wb As Workbook
Dim nr As Name
Dim myRange As Range

Set wb = ActiveWorkbook
Set nr = wb.Names.Item("Sheet1Amt1")

'These work if I
'Set myRange = Sheets("Sheet1").Range("E2:E41")
'Set myRange = Sheets("Sheet1").Range("E52:E91")

'nr.RefersTo = myRange      'Remove comment ' to activate if using myRange

' OR - give an absolute reference these will also work:
'nr.RefersTo = "=Sheet1!$E$2:$E$41"
'nr.RefersTo = "=Sheet1!$E$52:$E$91"

'This is what I would like to do is reference the code stored on another sheet:
nr.RefersTo = Sheets("sheet2").Range("B254").Value  'where B254 cell contains Sheet1!$E$2:$E$41
End Sub

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

 Sheet1!$E$2:$E$41

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

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

, I end up with this:

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

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?

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....

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

=INDIRECT(sheet1!$B$254)

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

 'cell 254B is =
sheet1!$E$2:$E$41
'cell 254C is =
sheet1!$E$2:$E$41
'and cell 254D is =
sheet1!$E$52:$E$91

Then I created the following sub routine

Sub IndirectFormula()
Dim ReferToString As String
ReferToString = Sheets("Code").Range("B254").Value
'Swap code lines for Indirect access from Name Manager 
    If ReferToString = Sheets("Code").Range("C254").Value Then
        Sheets("Code").Range("B254").Value = Sheets("Code").Range("D254").Value
    ElseIf ReferToString = Sheets("Code").Range("D254").Value Then
        Sheets("Code").Range("B254").Value = Sheets("Code").Range("C254").Value
    End If
End Sub

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