Updating Named Ranges with VBA using a list

I'm trying to update all of the Named Ranges in a workbook using a list in a spreadsheet and code. This is so I don't have to do them individually and there is over 300!!

I have tried to use the same syntax as appears in the Names dialog box but the ranges do not appear correctly and so will not work.

Any help with this would be fantastic as the cariable option isn't working!!! Thanks

This seems to work for me

Sub RenameNames()
Dim nme As Name
Dim i As Long
Dim iLastRow As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Set nme = Nothing
On Error Resume Next
Set nme = ActiveWorkbook.Names(Cells(i, "A").Value)
On Error GoTo 0
If Not nme Is Nothing Then
ActiveWorkbook.Names.Add Name:=Cells(i, "B").Value, _
RefersTo:=ActiveWorkbook.Names(Cells(i, "A").Value).RefersTo
ActiveWorkbook.Names(Cells(i, "A").Value).Delete
End If
Next i

End Sub


You might also be interested in Jan Karel Pieterse's NameManager utility at
http://www.jkp-ads.com/Download.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lee Palmer" <[email protected]> wrote
in message news:[email protected]...
>
> I'm trying to update all of the Named Ranges in a workbook using a list
> in a spreadsheet and code. This is so I don't have to do them
> individually and there is over 300!!
>
> I have tried to use the same syntax as appears in the Names dialog box
> but the ranges do not appear correctly and so will not work.
>
> Any help with this would be fantastic as the cariable option isn't
> working!!! Thanks
>
>
> --
> Lee Palmer
> ------------------------------------------------------------------------
> Lee Palmer's Profile:
http://www.excelforum.com/member.php...o&userid=29810
> View this thread: http://www.excelforum.com/showthread...hreadid=495207
>