Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
referring to a named range on another worksheet
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
referring to a named range on another worksheet
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.Coun t,.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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
referring to a named range on another worksheet
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") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
referring to a named range on another worksheet
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to specific cell in named range in Excel formula | Excel Worksheet Functions | |||
Do not specify worksheet in a named range | Excel Discussion (Misc queries) | |||
Getting a named range to appear in another worksheet | Excel Discussion (Misc queries) | |||
How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
named range not specific to worksheet | Excel Programming |