Going through Named Ranges in only specific Sheets in my WorkBook

[Aefavant]

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]

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.

 

[PatM]

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