Printing a List of Named Ranges

Walk away, have a bite to eat and all is revealed. Revised Procedure.

Sub ListNames()
[a1].CurrentRegion.ClearContents
[a1] = "Name"
[b1] = "Refers To"
[A2].ListNames
Dim i As Long, wks As Worksheet, tbl As ListObject
[a1].Select
i = Range("a1").End(xlDown).Row
For Each wks In Worksheets
For Each tbl In wks.ListObjects
i = i + 1
Cells(i, 1) = tbl.Name
Cells(i, 2) = tbl.Parent.Name & "!" & tbl.Range.Address
Next tbl
Next
Columns("A:B").AutoFit
End Sub

to all: List Table and Range Names:

Best to be done with a macro

Sub ListNames()
[a1] = "Name"
[b1] = "Refers To"
[A2].ListNames
Dim i As Long, wks As Worksheet, tbl As ListObject
[a1].Select
i = Range("a1").End(xlDown).Row
For Each wks In Worksheets
For Each tbl In wks.ListObjects
i = i + 1
Cells(i, 1) = tbl.Name
Next tbl
Next
End Sub

I haven't found how to get the table reference yet.

(see Figure 1 below)

This method fails to list all my defined names, just the cells that fall within ranges versus TABLES I assigned names for. How can I generate a complete list?

Excellent tip - so simple, yet saved hours of screen capturing to get the same info!

Very helpful thanks!!

I realize this is an old thread, but I just tried it. It works great - for names scoped to "workbook." I have a workbook that has many sheets with named ranges scoped to the individual sheets. Is there any way to list ALL the names on one sheet?

I do not use named ranges, but I use a lot of tables. How can I print list of table names and references?

Can you explain what Names may not appear in the list?
In a sheet started by someone else I can see 44 names in the "Name Manager". The printed list and naming box however only have 37 in them . 3 of the missing relate to Print_Area (1) and Print_Titles (2 on different sheets).
The other 4 when found on the relevant sheets show up in the naming box when the area is selected. One of these areas is differently noted in the "Name Manager - Refers to" than actually required to be selected as an area to appear in the naming box.

@Brian,
I assume it was a Typo on your side.
{A1].listnames is wrong ([A1].listnames is OK)
By the way - I usually don't suggest VBE stuff because 8 out of 10 of Excek users are not familiar with what you are talking about.
You will agree that, in this case, hitting [F3] and then 'Paste List' is somehow a little shortes procedure.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

In the VBE immediate window
{A1].listnames

@To whom it may concern,
Pressing <F3> and clicking 'Paste List' is a somehow shortes/faster.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL