Export from Name Manager?

Supersmithy

Board Regular
Joined
Feb 5, 2012
Messages
81
I'm not sure there is a native method to export them:


Here's Quick Code Sample to list them :

Code:
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

AHL

New Member
Joined
Nov 2, 2012
Messages
1
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

New Member
Joined
Aug 28, 2014
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,627
Messages
5,445,572
Members
405,341
Latest member
AzureStoneDog

This Week's Hot Topics

Top