Solved

vba to show the named range source address a new sheet

Posted on 2014-11-14
12
150 Views
Last Modified: 2014-11-14
Hi

 i am looking for a vba code that when i run in a workbook with so many named ranges, in a new sheet it would create a report like below

Name   like this  ProductRange
Name Sheet SheetName  like Sheet1 etc
Named range address like A1:D400

 in addition if it also can put the highlight the named range source background color

 thanks,.
0
Comment
Question by:Flora
  • 5
  • 4
  • 3
12 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40442598
Sorry not very clear what you want could you pls post a sample workbook first.

and ...
by this
Name   like this  ProductRange
 Name Sheet SheetName  like Sheet1 etc
 Named range address like A1:D400

You mean a macro that would ask the user something like:
Please enter ProductRange
Please enter SheetName
Please enter Range Address

and then it would create whatever in these values?

You need to be specific if you want us to build something for you
gowflow
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40442610
In prior versions you used to be able to get a list of range names from the Name menu. Can't see the equivalent from Name Manager in 2010.

The keyboard shortcuts from prior versions was:

Alt > I > N > P

So: Menu > Insert > Name > Paste List

Might be worth a try.

Thanks
Rob H
0
 
LVL 5

Author Comment

by:Flora
ID: 40442876
Thank you gowflow

here is the example attached.  on sheet("currently") is what i go  and on sheet("needed result with VBA") is what i need to get done by VBA
ee.xlsb
0
 
LVL 5

Author Comment

by:Flora
ID: 40442880
here is a similar another question that no one yet replied.  this one was for named range and this question link http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28562085.html  same thing but for pivot table.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40442887
No need for VBA
Look at sheet transpose if this is the result you want let me know I will tell you how to do it.
gowflow
ee.xlsb
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40442907
Flora - as suggested with shortcut keys, now that I am in a file with Range Names it works.

Insert a sheet and then:

Press Alt > I > N > P and then click Paste List.

This gives a list of Range Names and the range to which they refer in the =SheetName!Range format.

Doesn't do the colours unfortunately.

Thanks
Rob H
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 40442912
As an aside, I think your range names are out of alignment. For example, when I press F5 and Select Category_Name, the range for SP Name is highlighted.

Thanks
Rob H
0
 
LVL 5

Author Comment

by:Flora
ID: 40443141
Thank you Rob.

but i was thinking how to do the coloring.
0
 
LVL 5

Author Comment

by:Flora
ID: 40443144
Gowflow,

i am sorry that i am not really good in putting the questions correctly.

i have this code which i copied from internet.  this one does the listing but it does not do the color part.

is the color part doable? possible?  

Sub ListAllNames()
 
Dim myName As Name
 Dim intCount As Integer
 
Application.Worksheets.Add
 ActiveSheet.Name = "Workbook names"
 
Range("A1") = "Name"
 Range("B1") = "Reference"
 
With Range("A1:B1")
 .Font.Bold = True
 .Font.Underline = True
 End With
 
intCount = 2
 
For Each myName In ThisWorkbook.Names
 
Range("A" & intCount).Value = myName.Name
 Range("B" & intCount).Value = myName
 
intCount = intCount + 1
 Next
 
Range("A1:B1").EntireColumn.AutoFit
 
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 40443234
Sorry I have realized that I misunderstood your request anyhow this is what you need.

Sub GetNames()
Dim WS As Worksheet
Dim sNames As Name
Dim I As Long

Set WS = Sheets("Names")
WS.Range("A1") = "Name"
WS.Range("B1") = "Refers to"
WS.Range("C1") = "Sheet"
I = 2

For Each sNames In ActiveWorkbook.Names
    WS.Cells(I, "A") = sNames.Name
    WS.Cells(I, "B") = " " & sNames.RefersTo
    WS.Cells(I, "C") = Mid(sNames.RefersTo, 2, InStr(1, sNames.RefersTo, "!") - 2)
    I = I + 1
Next sNames

WS.UsedRange.EntireColumn.AutoFit
End Sub

Open in new window


Check the workbook attached in the sheet Name

If you want to copy this macro to any other workbook make sure you create a sheet and call it Name and then activate the macro.

Now if you like it I can make it self adjustable. Let me know.
gowflow
ee.xlsb
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40443263
Ooops I just saw your post !

Let me incorporate the colors ....

Here it is

Sub GetNames()
Dim WS As Worksheet
Dim sNames As Name
Dim I As Long

Worksheets.Add after:=Worksheets(Worksheets.Count)
Set WS = ActiveSheet
WS.Name = "Names " & Format(Now, "hhmmss")
WS.Range("1:" & WS.Rows.Count).EntireRow.Delete
WS.Range("A1") = "Name"
WS.Range("B1") = "Refers to"
WS.Range("C1") = "Sheet"
WS.Range("A1:C1").Font.Bold = True
I = 2

For Each sNames In ActiveWorkbook.Names
    WS.Cells(I, "A") = sNames.Name
    WS.Cells(I, "B") = " " & sNames.RefersTo
    WS.Cells(I, "C") = Mid(sNames.RefersTo, 2, InStr(1, sNames.RefersTo, "!") - 2)
    WS.Range("A" & I & ":C" & I).Interior.ColorIndex = I + 31
    I = I + 1
Next sNames

WS.UsedRange.EntireColumn.AutoFit
End Sub

Open in new window



This one takes also care of creating a worksheet for you so simply copy to any workbook and it will work regardless of any sheet.

gowflow
ee.xlsb
0
 
LVL 5

Author Comment

by:Flora
ID: 40443306
thanks gowflow
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
highlight duplicate entry 16 27
ms office troubleshooting for users 8 34
Excel Save As Status Box will not go away 6 17
Excel Automation VBA 19 25
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now