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

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

John Fuller wrote:
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

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)

"John Fuller" wrote:

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

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

NickHK

"John Fuller" wrote in message
ps.com...
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

Thanks all. I'll give them a try.


NickHK wrote:
John,
MsgBox Range("rngTest").Parent.Parent.Name=ThisWorkbook.N ame

NickHK

"John Fuller" wrote in message
ps.com...
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