Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print a list of defined names and what it refers to
Hi all,
Using excell 2007 on Vista Would like to print a list of defined names and associated data (value, refers to, comments) from a worksheet Have searched the web but come up empty. Closest was name manager but I can't find a version for excel 2007 When the define name dialog window is open print options don't seem to me to be accessible Skinman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print a list of defined names and what it refers to
sometimes its not to complicated..
Try this Range("A1").ListNames |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print a list of defined names and what it refers to
Thanks Curly. Very grateful
The formula worked for workbook names but not worksheet names Is it possible to amend the formula to include worksheet names? Thanks Skinman "CurlyDave" wrote in message ... sometimes its not to complicated.. Try this Range("A1").ListNames |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print a list of defined names and what it refers to
Hi
Here's VB Code which gets you the name list, reference and values Sub NamesLister() For i = 1 To ActiveWorkbook.Names.Count Sheets("Sheet1").Activate Cells(i, 1).Value = ActiveWorkbook.Names(i).Name Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address Cells(i, 3).Value = ActiveWorkbook.Names(i).RefersToRange.Value Next End Sub "Skinman" wrote: Hi all, Using excell 2007 on Vista Would like to print a list of defined names and associated data (value, refers to, comments) from a worksheet Have searched the web but come up empty. Closest was name manager but I can't find a version for excel 2007 When the define name dialog window is open print options don't seem to me to be accessible Skinman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print a list of defined names and what it refers to
Thanks BSc Chem Eng Rick ...
But failed to work for me. As soon as it comes to a #ref comes up with run time error value 1004 debug comes up for this line.... Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address I have a macro that defines a range for a filter each time I run the macro at the end of the macro that range is deleted leaving the defined range with #ref till the next time I run the macro. Thanks for your input though, much appreciated. Skinman. "BSc Chem Eng Rick" wrote in message ... Hi Here's VB Code which gets you the name list, reference and values Sub NamesLister() For i = 1 To ActiveWorkbook.Names.Count Sheets("Sheet1").Activate Cells(i, 1).Value = ActiveWorkbook.Names(i).Name Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address Cells(i, 3).Value = ActiveWorkbook.Names(i).RefersToRange.Value Next End Sub "Skinman" wrote: Hi all, Using excell 2007 on Vista Would like to print a list of defined names and associated data (value, refers to, comments) from a worksheet Have searched the web but come up empty. Closest was name manager but I can't find a version for excel 2007 When the define name dialog window is open print options don't seem to me to be accessible Skinman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print a list of defined names and what it refers to
Typically you get those #REF! errors after deleting entire rows or columns
of the named range. Attempting to refer to such results in an error. Simplest (crudest) is simply to add before the loop On Error Resume Next If you see REF as part of the address you'll know why you don't get an address this line - Cells(i, 3).Value = ActiveWorkbook.Names(i).RefersToRange.Value will error if the named range refers to more than one cell Try the NameManager addin which you can get from the authors' sites of Jan Karel Pieterse and Charles Williams: www.jkp-ads.com www.DecisionModels.com Regards, Peter T "Skinman" wrote in message ... Thanks BSc Chem Eng Rick ... But failed to work for me. As soon as it comes to a #ref comes up with run time error value 1004 debug comes up for this line.... Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address I have a macro that defines a range for a filter each time I run the macro at the end of the macro that range is deleted leaving the defined range with #ref till the next time I run the macro. Thanks for your input though, much appreciated. Skinman. "BSc Chem Eng Rick" wrote in message ... Hi Here's VB Code which gets you the name list, reference and values Sub NamesLister() For i = 1 To ActiveWorkbook.Names.Count Sheets("Sheet1").Activate Cells(i, 1).Value = ActiveWorkbook.Names(i).Name Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address Cells(i, 3).Value = ActiveWorkbook.Names(i).RefersToRange.Value Next End Sub "Skinman" wrote: Hi all, Using excell 2007 on Vista Would like to print a list of defined names and associated data (value, refers to, comments) from a worksheet Have searched the web but come up empty. Closest was name manager but I can't find a version for excel 2007 When the define name dialog window is open print options don't seem to me to be accessible Skinman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA defined named range not appearing in Names list... | Excel Programming | |||
Print list of ws names | New Users to Excel | |||
Multiple Names in refers to field of Define Name | Excel Discussion (Misc queries) | |||
List of defined names | Excel Worksheet Functions | |||
how to print defined cell names with comments, not numbers? | New Users to Excel |