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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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

Watch MrExcel Video

Forum statistics

Threads
1,095,225
Messages
5,443,197
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top