Hello,

I think you could use the "RefersToRange" property for this.

Loop through all your named ranges, then check which sheet they are on with
RefersToRange.
Then only do something to those named ranges on the proper sheets, using an
IF.

Option Explicit

Sub LoopThruRanges()

Dim rng As Variant
Dim n As Variant

Set rng = ThisWorkbook.Names

For Each n In rng
If n.RefersToRange.Worksheet.Name = "Sheet1" Then
Sheet1.Range("D2").Value = Sheet1.Range("B2").Value
'you could do whatever here
End If
Next n

End Sub


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