Named ranges on other sheets

[Conan Kelly]

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)?

 

[Die_Another_Day]

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

Charles

 

[Dave Peterson]

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").referstorange

===
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.

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)?

 

[Peter T]

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

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)?