Solved

vba to show the named range source address a new sheet

Posted on 2014-11-14
12
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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 6

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

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 33

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
 
LVL 33

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 6

Author Comment

by:Flora
ID: 40443141
Thank you Rob.

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

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 6

Author Comment

by:Flora
ID: 40443306
thanks gowflow
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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