excel learner
asked on
vba macro name ranges
Dear experts,
I have a file, which has got about 200 named ranges. The file is not confidential/proprietary infomation.
Can I please request a macro which name the named range in column A, then range to which the named range pertains. In the macro you provide other details also to be included, which I may not know are important.
The macro will do a great in understanding methology and will be greatly appreciated.
Thank you
I have a file, which has got about 200 named ranges. The file is not confidential/proprietary infomation.
Can I please request a macro which name the named range in column A, then range to which the named range pertains. In the macro you provide other details also to be included, which I may not know are important.
The macro will do a great in understanding methology and will be greatly appreciated.
Thank you
ASKER
Hi fanpages,
I need a macro which could do the following:
1. List the several 'name ranges'
2. Provide details like sheet name and cell range to which the name pertains to.
If the expert believes that a name range could include other details (not requested be me in either 1 or 2), then the macro could list the same
The output shall be provided by the macro by creating a new sheet in the file.
Sorry for my negligence.
Thank you
I need a macro which could do the following:
1. List the several 'name ranges'
2. Provide details like sheet name and cell range to which the name pertains to.
If the expert believes that a name range could include other details (not requested be me in either 1 or 2), then the macro could list the same
The output shall be provided by the macro by creating a new sheet in the file.
Sorry for my negligence.
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi fanpages,
Thank you for the comment, but I am getting an error and unable to run the code referred by you.
I found the below code from ID: 20473902.
Sub ListNamedRanges()
Dim wb As Workbook, ws As Worksheet, sh As Worksheet, nm As Name
Set wb = ActiveWorkbook
Set ws = wb.Worksheets.Add
ws.Range("A1:C1").Value = Array("Name", "Refers To", "Sheet/Workbook")
For Each nm In wb.Names
With ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
.Value = nm.Name
.Offset(0, 1) = "'" & nm.RefersTo
.Offset(0, 2) = wb.Name
End With
Next nm
For Each sh In wb.Worksheets
For Each nm In sh.Names
With ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
.Value = nm.Name
.Offset(0, 1) = "'" & nm.RefersTo
.Offset(0, 2) = sh.Name
End With
Next nm
Next sh
ws.UsedRange.AutoFormat Format:=xlRangeAutoFormatS imple
End Sub
The code provided me with the required output.
Thank you
Thank you for the comment, but I am getting an error and unable to run the code referred by you.
I found the below code from ID: 20473902.
Sub ListNamedRanges()
Dim wb As Workbook, ws As Worksheet, sh As Worksheet, nm As Name
Set wb = ActiveWorkbook
Set ws = wb.Worksheets.Add
ws.Range("A1:C1").Value = Array("Name", "Refers To", "Sheet/Workbook")
For Each nm In wb.Names
With ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
.Value = nm.Name
.Offset(0, 1) = "'" & nm.RefersTo
.Offset(0, 2) = wb.Name
End With
Next nm
For Each sh In wb.Worksheets
For Each nm In sh.Names
With ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
.Value = nm.Name
.Offset(0, 1) = "'" & nm.RefersTo
.Offset(0, 2) = sh.Name
End With
Next nm
Next sh
ws.UsedRange.AutoFormat Format:=xlRangeAutoFormatS
End Sub
The code provided me with the required output.
Thank you
Hi again,
I am glad you found a working solution in any respect.
What error message did you see with the code hosted at MrExcel.com?
BFN,
fp.
I am glad you found a working solution in any respect.
What error message did you see with the code hosted at MrExcel.com?
BFN,
fp.
I appreciate that English may not be your first language, but I am struggling to understand your requirements (within the first sentence in this paragraph):
Please can you either provide an example of what you require, or re-word your requirements?
Thanks.
BFN,
fp.