VBA to create multiple range names

[Ummm1]

Hi,

I am converting a list of about 100 static named ranges to dynamic named ranges.
Within my workbook of data sheets I have a spreadsheet with the range names in one column and the new dynamic ranges in another.
The ranges are in this format: "$A$A:INDEX($A$A,COUNTA($A$A)".
I would like to use VBA to go through the names list and convert each static range to its dynamic range.
In the workbook there are thousands of formulas that refer to the static named ranges.
Apparently I know just enough VBA to spend a bunch of time getting nowhere.
Can anyone help? Thank you!

 

[Ummm1]

Oops I mean this format (row 2 range start): $A$2:INDEX($A$A,COUNTA($A$A)

 

[JoeMo]

Untested, but maybe something like this. Change the range E1:F3 to the range occupied by your 'lookup table' that has range names in one column and the new dynamic ranges in the next column to the right of the names.

Sub ChangeRangeNamesRefersTo()
Dim Nm As Name
For Each Nm In ActiveWorkbook.Names
    On Error Resume Next
    Nm.RefersTo = Application.VLookup(Nm.Name, Range("E1:F3"), 2, 0)  'change range to suit
    On Error GoTo 0
Next Nm
End Sub

 

[Ummm1]

Joe, that works great; thank you very much!
--Arturo

 

[JoeMo]

Joe, that works great; thank you very much!
--Arturo

You are welcome - thanks for the reply.