List of all names in EXCEL workbook
 

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.


"arno" wrote:

Menu Insert/Names/Insert/Insert List

arno

"NameSearcher" schrieb im
Newsbeitrag ...
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.

List of all names in EXCEL workbook
 

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)


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


"arno" wrote:

Menu Insert/Names/Insert/Insert List

arno

"NameSearcher" schrieb im
Newsbeitrag ...
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.

List of all names in EXCEL workbook
 

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)


"NameSearcher" wrote:

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.


"arno" wrote:

Menu Insert/Names/Insert/Insert List

arno

"NameSearcher" schrieb im
Newsbeitrag ...
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.

List of all names in EXCEL workbook
 

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

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


"arno" wrote:

Menu Insert/Names/Insert/Insert List

arno

"NameSearcher" schrieb im
Newsbeitrag ...
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.

List of all names in EXCEL workbook
 

Thanks Peter T and Arno: that does work!

"Peter T" wrote:

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

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


"arno" wrote:

Menu Insert/Names/Insert/Insert List

arno

"NameSearcher" schrieb im
Newsbeitrag ...
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.