Export from Name Manager?

eileen22

New Member
Joined
Jun 1, 2011
Messages
6
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!!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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
 

eileen22

New Member
Joined
Jun 1, 2011
Messages
6
Wow! That worked perfectly and on the first try! You saved me sooo much time. THANK YOU!
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,267
the short cut keys make explanation easier

going the other way a job for VBA, I think
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
Thank you, that is what I thought.

Wishful thinking. ;)
 

eileen22

New Member
Joined
Jun 1, 2011
Messages
6
Shortcut keys - even better. Thanks a ton everyone, I love it when life gets easier :)
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,973
Insert > Name > Paste > List will list all of the names onto the active sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,509
Messages
5,414,980
Members
403,560
Latest member
dallash

This Week's Hot Topics

Top