Updating Named Ranges with VBA using a list
 

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

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

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

--
Lee Palme
-----------------------------------------------------------------------
Lee Palmer's Profile: http://www.excelforum.com/member.php...fo&userid=2981
View this thread: http://www.excelforum.com/showthread.php?threadid=49520

Updating Named Ranges with VBA using a list
 

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" wrote
in message ...

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