Ensure Named Range is in ThisWorkbook

[John Fuller]

I'm working on a project where I will have numerous workbooks open with
lots of named ranges. I'm trying to figure out how to ensure that when
I call a named range, ie:

dim myRange as Range
set myRange = Range("Named Range")

that I am getting the range in the workbook that the macro is written
in. Really, I want this:

dim myRange as Range
set myRange = Range(ThisWorkbook."Named Range")

but it obviously doesn't work like that (just I hope an easy way of
explaining what I'm looking for).

Thanks in advance,

John

 

[acampbell012]

John,

John:

In the Thisworkbook module, tests for range in the active workbook:

Sub rangetest()
On Error Resume Next
If Range("Test").Name Is Nothing Then
MsgBox "Range does not exist"
Else:
MsgBox "Range does exist"
End If
End Sub

Alan

 

[Guest]

John, use thiskind ofscript :

Sub test()
Dim rMyRange As Range
Set rMyRange = ThisWorkbook.Names("myRange").RefersToRange
End Sub

where "ThisWorkbook"is the default "CodeName" for the workbook in the visual
basic project (but it can be modified to be clearer like wkbDataTransfered)

 

[NickHK]

John,
MsgBox Range("rngTest").Parent.Parent.Name=ThisWorkbook.Name

NickHK

 

[John Fuller]

Thanks all. I'll give them a try.

John,
MsgBox Range("rngTest").Parent.Parent.Name=ThisWorkbook.Name

NickHK