How to List Named-Ranges only from specific Sheet

[aefavant]

Hi all!

Still 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---------------

Thanks!

[lynnnow]

You need to change ThisWorkbook.Names to ActiveSheet.Names

[aefavant]

You need to change ThisWorkbook.Names to ActiveSheet.Names

It didn't work either ;(
I also tried ActiveSheet("xxxx").Names, to no good. It apparently does not recognise any ranges in sheetxxx

[lynnnow]

try this

[VBA]'Set rng_names = ThisWorkbook.Names
For Each n In ActiveSheet.Names
MsgBox n
Next n[/VBA]