hello,
I'm trying to set up a spreadsheet where a specific named range will change depending on the value of a cell.
Thanks to SydneyGeek for the code below but I'm looking to further enhance this. In my NameList I'm generating the new name for the range(the range, in this case, will always be a single cell) in the right column using =Concatenate "newName"&B2 to provide me with the new name. this gives me the new name of the range plus the number in B2 (which will always be 1-30) I then want to dynamically change the "Oldname" on the left-hand side of the Namelist to the new name (so that the code will work next time there is a change also)
Any suggestions gratefully accepted and thanks in anticipation.
I'm trying to set up a spreadsheet where a specific named range will change depending on the value of a cell.
Thanks to SydneyGeek for the code below but I'm looking to further enhance this. In my NameList I'm generating the new name for the range(the range, in this case, will always be a single cell) in the right column using =Concatenate "newName"&B2 to provide me with the new name. this gives me the new name of the range plus the number in B2 (which will always be 1-30) I then want to dynamically change the "Oldname" on the left-hand side of the Namelist to the new name (so that the code will work next time there is a change also)
Any suggestions gratefully accepted and thanks in anticipation.
VBA Code:
Sub NameChanger()
Dim arNames()
Dim nm As Name
Dim i As Integer
Dim sname As String
sname = ActiveSheet.Name
arNames = Sheets(sname).Range("NameList").Value
For i = LBound(arNames) To UBound(arNames)
For Each nm In ActiveWorkbook.Names
If nm.Name = arNames(i, 1) Then
nm.Name = arNames(i, 2)
End If
Next nm
Next i
End Sub