Finding All Range Names on a given sheet

L

LAF

I am trying to find all range names that exist on a given sheet in m
workbook. I do not need to know the names for all of the sheets. Her
is the code I used, but it only displayed 1 of the names on that sheet
when in fact there are about 5. Can anyone help?

Sub testbsrange()
Dim oname As Object
For Each oname In Worksheets("Blank_Sheet1").Names
MsgBox oname.Name & oname.RefersToR1C1

Next oname
End Su
 
T

Tom Ogilvy

Sub testbsrange()
Dim oname As Object
Dim rng as Range
For Each oname In worksheets("Blank_Sheet1").parent.Names
set rng = Nothing
on Error Resume Next
set rng = oname.RefersToRange
On Error goto 0
if not rng is nothing then
if lcase(rng.parent.name) = "blank_sheet1" then
MsgBox oname.Name & oname.RefersToR1C1
end if
End if
Next oname
End Sub
 
Top