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!!

 

[Supersmithy]

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

The code above from Tweedle is fantastic. Is there an edit that I could make to it so that the 'refers to' column of cell ranges are returned as working hyperlinks?

Thanks,

Luke

 

[AHL]

In Excel2007, to take the list you just pasted back into Name Manager, select the range you just pasted, go to the Formula tab/Create from Selection, the box should be checked for "Create names from values in the: Top row". It will ask you if you want to replace the existing names. Yes.

 

[JamesDandy]

Although this thread was answered a long time ago, it was important to something I was doing recently however I made a slight change in order to suit my needs.

Because I wanted the ability to use this code in any other workbook I might be working in, I stored the module in my Personal.xlsb file. This required the need to change the term ThisWorkbook.Names to ActiveWorkbook.Names.

I also preferred the VBA code since in some cases it's valuable to know the scope, which the Alt-I-N-P method ignores, and it also ignores the print range names as well.