Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through Named Ranges in only specific Sheets in my WorkBook
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through Named Ranges in only specific Sheets in my WorkBook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through Named Ranges in only specific Sheets in my WorkBook
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges in various sheets | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Print sheets by "All Sheets in workbook, EXCEPT for specific named sheets". Possible? | Excel Programming | |||
Named ranges on other sheets | Excel Programming | |||
named ranges and copying sheets to another workbook | Excel Programming |