PDA

View Full Version : How to List Named-Ranges only from specific Sheet



aefavant
05-12-2010, 07:43 AM
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
05-12-2010, 07:47 AM
You need to change ThisWorkbook.Names to ActiveSheet.Names

aefavant
05-12-2010, 07:56 AM
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
05-12-2010, 08:01 AM
try this

'Set rng_names = ThisWorkbook.Names
For Each n In ActiveSheet.Names
MsgBox n
Next n