referring to a named range on another worksheet

[Guest]

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

 

[Guest]

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

 

[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 :

 

[Guest]

Thank you very much, your post was very helpful.

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 :