Solved

vba to show the named range source address a new sheet

Posted on 2014-11-14
12
148 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 31

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 31

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

759 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

21 Experts available now in Live!

Get 1:1 Help Now