Named ranges on other sheets
 

Hello all,

I have given cells A1070:B1070 on sheets 2, 3, 4 unique names (6 names, 2 cells on each sheet). In my code I can use
"Range([RangeName])" to refer to the cells on the active sheet only. In order to refer to named ranges on other sheets I need to
use "Worksheets([SheetName]).Range([RangeName])".

Does anyone know why this is so? If each cell on the 3 different sheets has a unique name, why do I need to tell it which sheet to
look at? Is there any way arond this (other than declaring 6 range object variables and setting them = to each named range)?

--
Thanks for any help anyone can provide,

Conan Kelly

Named ranges on other sheets
 

What exactly are you trying to accomplish? Can you paste the code that
you have dealing with the named range?

Charles

Conan Kelly wrote:
Hello all,

I have given cells A1070:B1070 on sheets 2, 3, 4 unique names (6 names, 2 cells on each sheet). In my code I can use
"Range([RangeName])" to refer to the cells on the active sheet only. In order to refer to named ranges on other sheets I need to
use "Worksheets([SheetName]).Range([RangeName])".

Does anyone know why this is so? If each cell on the 3 different sheets has a unique name, why do I need to tell it which sheet to
look at? Is there any way arond this (other than declaring 6 range object variables and setting them = to each named range)?

--
Thanks for any help anyone can provide,

Conan Kelly

Named ranges on other sheets
 

I find:

dim myRng as range
set myrng = worksheets("sheet999").range("somerangename")

more useful (more self documenting??) to use than:

dim myRng as range
set myrng = activeworkbook.names("somerangename").referstorang e

===
But ranges belong to worksheets. Either you explicitly specify the worksheet
that contains that range or you rely on the excel's treatment of unqualified
ranges (unqualified ranges will belong to the activesheet -- if the code is in a
General module).


You could also rely on what the activeworkbook is and use something like:

MsgBox Application.Range("test1").Address(external:=True)
MsgBox Application.Range("test2").Address(external:=True)

But I think the more you qualify stuff, the better:

dim myRng as range
dim wkbk as workbook
set wkbk = workbooks("somebook.xls")
set myrng = wkbk.worksheets("sheet999").range("somerangename")

Is about as safe as I get.



Conan Kelly wrote:

Hello all,

I have given cells A1070:B1070 on sheets 2, 3, 4 unique names (6 names, 2 cells on each sheet). In my code I can use
"Range([RangeName])" to refer to the cells on the active sheet only. In order to refer to named ranges on other sheets I need to
use "Worksheets([SheetName]).Range([RangeName])".

Does anyone know why this is so? If each cell on the 3 different sheets has a unique name, why do I need to tell it which sheet to
look at? Is there any way arond this (other than declaring 6 range object variables and setting them = to each named range)?

--
Thanks for any help anyone can provide,

Conan Kelly

--

Dave Peterson

Named ranges on other sheets
 

Go with Dave's excellent advice but I expect the reason for the scenario you
describe is because you are using worksheet level (local) names and not
workbook level (global).

Regards,
Peter T

"Conan Kelly" <CTBarbarin at msn dot com wrote in message
...
Hello all,

I have given cells A1070:B1070 on sheets 2, 3, 4 unique names (6 names, 2
cells on each sheet). In my code I can use
"Range([RangeName])" to refer to the cells on the active sheet only. In
order to refer to named ranges on other sheets I need to
use "Worksheets([SheetName]).Range([RangeName])".

Does anyone know why this is so? If each cell on the 3 different sheets
has a unique name, why do I need to tell it which sheet to
look at? Is there any way arond this (other than declaring 6 range object
variables and setting them = to each named range)?

--
Thanks for any help anyone can provide,

Conan Kelly