• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

vba to show the named range source address a new sheet

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
Flora
Asked:
Flora
  • 5
  • 4
  • 3
1 Solution
 
gowflowCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
FloraAuthor Commented:
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
Technology Partners: 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!

 
FloraAuthor Commented:
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
 
gowflowCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
FloraAuthor Commented:
Thank you Rob.

but i was thinking how to do the coloring.
0
 
FloraAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
FloraAuthor Commented:
thanks gowflow
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now