List of all names in EXCEL workbook

[Guest]

How do I get a list of all the names being used in an EXCEL workbook? The
"Insert\Names... " sequence of commands gets a list you can view on the
screen, about 10 at a time. I would like to copy/paste the whole list. I
would also like the list to include (as a separate column) associated text
showing the references for each name.

 

[arno]

Menu Insert/Names/Insert/Insert List

arno

 

[Guest]

Suggestion is on point but I can't seem to bring up the sequence. I have
EXCEL 2000. I have tried the Tools\Customize\ command and tried to set up a
custom menu bar with the Insert List but I haven't been able to get it to
work. Maybe walk me through it slower. Thanks.

 

[Bob Phillips]

VBA?

Sub NamesList()
Dim sh As Worksheet
Dim nme As Name
Dim i As Long

On Error Resume Next
Set sh = Worksheets("Names List")
If sh Is Nothing Then
Worksheets.Add.Name = "Names List"
End If
On Error GoTo 0
With Worksheets("Names List")
For Each nme In ActiveWorkbook.Names
i = i + 1
.Cells(i, "A").Value = nme.Name
.Cells(i, "B").Value = nme.RefersTo
Next nme
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi,
I had the same problem to manage a huge list of names.
I have found 2 solutions:
1) I made a macro that add a new worksheet and then recurse all the names
writing the info of each name on a single row of the new wks:
Set oWksSource = Application.ActiveWorkbook
Set oWksOutput = Application.Workbooks.Add(xlWorksheet).Worksheets(1)
For Each oName In oWksSource.Names
sName = oName.Name
sRif = oName.RefersTo
bVisibile = oName.visible
oWksOutput.Cells(lRow, 1).Value = sName
oWksOutput.Cells(lRow, 2).Value = "'" & sRif
oWksOutput.Cells(lRow, 3).Value = bVisibile
lRow = lRow + 1
Next oName

2) a freeware utility: NameManager (www.jkp-ads.com)

 

[Peter T]

I suspect arno meant:
Insert / Name / Paste... / Paste List

or in a macro:
ActiveCell.ListNames

If "Paste." is greyed out, or the above code does nothing, it means no
non-hidden Workbook-level names exist and no Worksheet-level names exist on
the active sheet. But Worksheet-level names may or may not exist on other
sheets. Only way to fully confirm is activating other sheets and check
similar, or with a macro as others have suggested. And only with a macro to
check any hidden names.

Regards,
Peter T

 

[Guest]

Thanks Peter T and Arno: that does work!

I suspect arno meant:
Insert / Name / Paste... / Paste List

or in a macro:
ActiveCell.ListNames

If "Paste." is greyed out, or the above code does nothing, it means no
non-hidden Workbook-level names exist and no Worksheet-level names exist on
the active sheet. But Worksheet-level names may or may not exist on other
sheets. Only way to fully confirm is activating other sheets and check
similar, or with a macro as others have suggested. And only with a macro to
check any hidden names.

Regards,
Peter T

 

[Dave Peterson]

And if you're working with names, do yourself a giant favor and get Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp