Solved

vba to show the named range source address a new sheet

Posted on 2014-11-14
12
151 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 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 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