How to load multiple names ?

[Kallysta]

Excel 2003 question.
Hello,
I have a file with about 200 names. I pasted the list of these names
(Insert/Name/Paste...) in a tab. In the first column, there are the names and
in the second one, the corresponding ranges.
As I have to change many ranges, I manually changed them in the pasted list.
Now I would like to reload all the names in once but I do not know if it is
possible and how to do it. It is really too long to change all name ranges
one by one with the Insert/Name/Define.
If not possible, is a macro able to perform it ?
Thank for your help.

 

[JLatham]

If I understand your explanation, it is certainly possible for a macro to
define the range that a name refers to.
As I understand it, you'd have entries like
A B
Bill $R$5
Jane $R$6:$T$6

that kind of thing? If that's the case, yes a macro can work through the
list in column A and define it to .RefersTo the range in the same row in
column B.

Something like this should do it:

Sub DefineNames()
Const sName = "'my Sheet'!"
Dim newReference As String
Dim lastRow As Long
Dim nameRange As String
Dim listOfNames As Range
Dim anyName As Range
'assumes names in A start at row 2
Set listOfNames = ActiveSheet.Range("A2:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
For Each anyName In listOfNames
newReference = "=" & sName & anyName.Offset(0, 1)
ActiveWorkbook.Names.Add Name:=anyName, _
RefersTo:=newReference
Next
Set listOfNames = Nothing
End Sub

Make sure that the ranges in column B are entered as absolutes, as shown
previously. And, yes, if you change the entries in B and run the macro
again, the name reference is changed, not just .Add(ed).

 

[Charles Williams]

You can do this with Name Manager using the List button to create a
worksheet containing all the Names, then change the refers-to on this sheet
and reload them using the Pickup button.

Download Name manager from
http://www.decisionmodels.com/downloads.htm

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com