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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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,328
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
23,302
Insert > Name > Paste > List will list all of the names onto the active sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,960
Messages
5,447,568
Members
405,456
Latest member
melmelseh

This Week's Hot Topics

Top