Export from Name Manager?

[eileen22]

Hello,

Looks like this question was asked in 2009 with no replies. Is there a way to export your list of table names and refers to formulas from Name Manager?

I would like to bulk manipulate some of them in a spreadsheet.

Thanks!!

 

[tweedle]

I'm not sure there is a native method to export them:


Here's Quick Code Sample to list them :

Sub ListNamedRanges()
Dim nm As Name
Set newws = Sheets.Add
newws.Activate
newws.Name = Replace(newws.Name, "Sheet", "NamedRanges")
'Labels
Cells(9, 1) = "Name"
Cells(9, 2) = "Refers To"
Cells(9, 3) = "Scope"

'List Ranges
RowIdx = 10: ColIdx = 1
    For Each nm In ThisWorkbook.Names
        
        If Left(nm.Name, 5) = "Sheet" Then
            x = Split(nm.Name, "!")
            Scope = x(0)
            vScope = "WorkSheet"
            rName = x(1)
        Else
            Scope = "Workbook"
            vScope = Scope
            rName = nm.Name
        End If
        
        Cells(RowIdx, ColIdx) = rName: ColIdx = ColIdx + 1
        Cells(RowIdx, ColIdx) = "'" & nm.RefersTo: ColIdx = ColIdx + 1
        Cells(RowIdx, ColIdx) = Scope: ColIdx = ColIdx + 1
    RowIdx = RowIdx + 1: ColIdx = 1
    Next
    
newws.UsedRange.Columns.EntireColumn.AutoFit
End Sub

 

[eileen22]

Wow! That worked perfectly and on the first try! You saved me sooo much time. THANK YOU!

 

[Fazza]

What about ALT-I-N-P then ALT-L

 

[Nalani]

What about ALT-I-N-P then ALT-L

That was Slick, using shortcut keys.

Anticipating the next question on the subject.

Once ranges are adjusted/changed, can the list be Imported back into Name Manager?

 

[Fazza]

the short cut keys make explanation easier

going the other way a job for VBA, I think

 

[Nalani]

Thank you, that is what I thought.

Wishful thinking.

 

[eileen22]

Shortcut keys - even better. Thanks a ton everyone, I love it when life gets easier

 

[tweedle]

It surely works.
Nothing I would ever remember when I needed it; actually took me less time to write the code than go through the 2003-2007 reference from M$ and ultimately find the 2007 Help (It's listed under "Auditing Names" if ne1 in the far future is looking.)

 

[mikerickson]

Insert > Name > Paste > List will list all of the names onto the active sheet.