referring to a named range on another worksheet

[Virginia]

i am getting an error message in my code when trying to refer to a named
range in a different worksheet (not the active sheet). i have a variable
"Rng" that refers to a non-contiguous named range on sheet1. a macro on a
form control is attempting to copy this range and paste it to sheet2. do i
always have to refer to the sheet name with a named range? i.e.
sheets("Sheet1").Range("MyRange")

[Patrick Molloy]

to copy a range from one sheet to another. Assuming rng is a range object

set rng = worksheets("sheet1").Range("b2:m15")

With rng


Worksheets("Sheet2").Range("A1").Resize(.Rows.Count,.Columns.Count).Value _
= .Value

End With





"Virginia" wrote:

> i am getting an error message in my code when trying to refer to a named
> range in a different worksheet (not the active sheet). i have a variable
> "Rng" that refers to a non-contiguous named range on sheet1. a macro on a
> form control is attempting to copy this range and paste it to sheet2. do i
> always have to refer to the sheet name with a named range? i.e.
> sheets("Sheet1").Range("MyRange")

[keepITcool]

Virginia:

first an important lesson in the theory of names

names exist in 2 places:

at the worksheet level 'local' name
at the workbook level 'global' name

all the names are members of the workbook's
names collection.

local names look like: sheet1!aName
global names look like: bName

for local names you'll see the sheetname
in the right column of the define names dialog

local names can be called from their 'own' sheet
without the sheet prefix.

HOWEVER:
you cannot access the GLOBAL name if a LOCAL name exist
on the activesheet with the SAME name

SO you should keep local and global ranges
separate.. and avoid 'duplicates'

Excel does not make this easy. Names are defined
as global by default. and when you copy a sheet
that contains global names, the NEW sheet will have
local names and the original sheet will retain the global names.

Confusing?..

download NameManager addin from www.jkp-ads.com
to help you manage names. Indispensible.


so back to your problem:
when you want to have the same name "rng" on multiple sheets
ensure that they are local names and that there is NO global name "rng"
(use NameManager for this!)

If and when the above is true...
(assuming names in activeworkbook)

strRngAddress = Range("sheet1!rng"),address
names.add "sheet2!rng", refersto = strRngAddress
or
worksheets(2).names.add "rng", _
refersto:=worksheets(1).Range("rng").address


there is another problem.. when you add a name it's "refersto"
argument is limited in length to 255 chars in r1c1 notation
.... which may represent a problem for more complicated multiarea ranges.

if so try following tedious method to copy the name:

Sub CreateAcomplicateName()
Dim i&, rg As Range
Worksheets(1).Activate
Set rg = Cells(1)
For i = 6 To 500 Step 5
Set rg = Union(rg, Cells(i, 1))
Next
rg.Name = "'" & ActiveSheet.Name & "'!test"
End Sub

Sub CopyAComplicatedName()
Dim rg As Range
Worksheets(1).Activate
Set rg = Range("'" & ActiveSheet.Name & "'!test")
rg.Select
Worksheets(2).Select False
Worksheets(2).Activate
Worksheets(2).Select True
Set rg = Selection
rg.Name = "'" & ActiveSheet.Name & "'!test"
End Sub


long story what..
Names ARE handy.. but complicated and imo somewhat buggy.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Virginia wrote :

> i am getting an error message in my code when trying to refer to a
> named range in a different worksheet (not the active sheet). i have
> a variable "Rng" that refers to a non-contiguous named range on
> sheet1. a macro on a form control is attempting to copy this range
> and paste it to sheet2. do i always have to refer to the sheet name
> with a named range? i.e. sheets("Sheet1").Range("MyRange")

[Virginia]

Thank you very much, your post was very helpful.

"keepITcool" wrote:

> Virginia:
>
> first an important lesson in the theory of names
>
> names exist in 2 places:
>
> at the worksheet level 'local' name
> at the workbook level 'global' name
>
> all the names are members of the workbook's
> names collection.
>
> local names look like: sheet1!aName
> global names look like: bName
>
> for local names you'll see the sheetname
> in the right column of the define names dialog
>
> local names can be called from their 'own' sheet
> without the sheet prefix.
>
> HOWEVER:
> you cannot access the GLOBAL name if a LOCAL name exist
> on the activesheet with the SAME name
>
> SO you should keep local and global ranges
> separate.. and avoid 'duplicates'
>
> Excel does not make this easy. Names are defined
> as global by default. and when you copy a sheet
> that contains global names, the NEW sheet will have
> local names and the original sheet will retain the global names.
>
> Confusing?..
>
> download NameManager addin from www.jkp-ads.com
> to help you manage names. Indispensible.
>
>
> so back to your problem:
> when you want to have the same name "rng" on multiple sheets
> ensure that they are local names and that there is NO global name "rng"
> (use NameManager for this!)
>
> If and when the above is true...
> (assuming names in activeworkbook)
>
> strRngAddress = Range("sheet1!rng"),address
> names.add "sheet2!rng", refersto = strRngAddress
> or
> worksheets(2).names.add "rng", _
> refersto:=worksheets(1).Range("rng").address
>
>
> there is another problem.. when you add a name it's "refersto"
> argument is limited in length to 255 chars in r1c1 notation
> .... which may represent a problem for more complicated multiarea ranges.
>
> if so try following tedious method to copy the name:
>
> Sub CreateAcomplicateName()
> Dim i&, rg As Range
> Worksheets(1).Activate
> Set rg = Cells(1)
> For i = 6 To 500 Step 5
> Set rg = Union(rg, Cells(i, 1))
> Next
> rg.Name = "'" & ActiveSheet.Name & "'!test"
> End Sub
>
> Sub CopyAComplicatedName()
> Dim rg As Range
> Worksheets(1).Activate
> Set rg = Range("'" & ActiveSheet.Name & "'!test")
> rg.Select
> Worksheets(2).Select False
> Worksheets(2).Activate
> Worksheets(2).Select True
> Set rg = Selection
> rg.Name = "'" & ActiveSheet.Name & "'!test"
> End Sub
>
>
> long story what..
> Names ARE handy.. but complicated and imo somewhat buggy.
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Virginia wrote :
>
> > i am getting an error message in my code when trying to refer to a
> > named range in a different worksheet (not the active sheet). i have
> > a variable "Rng" that refers to a non-contiguous named range on
> > sheet1. a macro on a form control is attempting to copy this range
> > and paste it to sheet2. do i always have to refer to the sheet name
> > with a named range? i.e. sheets("Sheet1").Range("MyRange")
>