Names don't just have to refer to ranges.

Option Explicit
Sub LoopThruRanges()

Dim myName As Name
Dim TestRng As Range

For Each myName In ThisWorkbook.Names
Set TestRng = Nothing
On Error Resume Next
Set TestRng = myName.RefersToRange
On Error GoTo 0

If TestRng Is Nothing Then
'skip it, it doesn't refer to a range
Else
If TestRng.Parent.Name = Worksheets("onlyforthissheet").Name Then
'do what you want
MsgBox "found one!" & vbLf & myName.Name
End If
End If
Next myName

End Sub

=========

If your names were all local to individual sheets, you could just loop through
the names collection for that sheet.


Aefavant wrote:

Hi all!

So, having trouble with ranges...
I can't seem to get right a way to run through Named Ranges that belong to
only a given Sheet...and then do stuff to them.
Any ideas??

'--------sub beg-------------
Sub LoopThruRanges()

Dim rng_names As Variant
Dim n As Variant


Set rng_names = ThisWorkbook.Names


For Each n In rng_names
With Sheets("OnlyforThisSheet")
MsgBox n 'debug
End With
Next

End Sub
'--------sub end---------------

I want to run a routine for only those ranges that are inside one (or a few)
Sheets I will specify, instead of bringing on all named ranges.

Thanks!

--

Dave Peterson