Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print list of names of all sheets in a workbook | Excel Discussion (Misc queries) | |||
workbook names list | Excel Discussion (Misc queries) | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
How do I display list of tab names used in a workbook on a sheet | Excel Worksheet Functions | |||
How do I print a list of worksheet tab names in a workbook | Excel Worksheet Functions |