Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges at workbook and worksheet levels
this i should know, but never get right...
a range can be named at a workBOOK or workSHEET level (true?) ....this presumes that a name could either exist at the workbook level and have the same value on all of the worksheets or or the same name could exist on each worksheet and have a value specific to that sheet (is this correct?) i use this to define a name at the workbook level: ActiveWorkbook.Names.Add Name:="SuspendProgrammedAutomaticCalculation", _ RefersToR1C1:="=""True""" what would the correct code be for creating a worksheet level name thanks in advance mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges at workbook and worksheet levels
I just turned on the macro recorder, created a local name that refers to
Sheet1!$A$1, switched off the recorder and reviewed the code which showed: ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _ RefersToR1C1:= "=Sheet1!R1C1" So it seems that you just have to specify the worksheet name in front of the range name, in order to localise it to that sheet. -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... this i should know, but never get right... a range can be named at a workBOOK or workSHEET level (true?) ...this presumes that a name could either exist at the workbook level and have the same value on all of the worksheets or or the same name could exist on each worksheet and have a value specific to that sheet (is this correct?) i use this to define a name at the workbook level: ActiveWorkbook.Names.Add Name:="SuspendProgrammedAutomaticCalculation", _ RefersToR1C1:="=""True""" what would the correct code be for creating a worksheet level name thanks in advance mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges at workbook and worksheet levels
This also works:
ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="MyRange", _ RefersToR1C1:="=Sheet1!R1C1" -- Return email address is not as DEEP as it appears "Jack Schitt" wrote in message ... I just turned on the macro recorder, created a local name that refers to Sheet1!$A$1, switched off the recorder and reviewed the code which showed: ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _ RefersToR1C1:= "=Sheet1!R1C1" So it seems that you just have to specify the worksheet name in front of the range name, in order to localise it to that sheet. -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... this i should know, but never get right... a range can be named at a workBOOK or workSHEET level (true?) ...this presumes that a name could either exist at the workbook level and have the same value on all of the worksheets or or the same name could exist on each worksheet and have a value specific to that sheet (is this correct?) i use this to define a name at the workbook level: ActiveWorkbook.Names.Add Name:="SuspendProgrammedAutomaticCalculation", _ RefersToR1C1:="=""True""" what would the correct code be for creating a worksheet level name thanks in advance mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges at workbook and worksheet levels
currently i use this method:
If [FeeReviewAtTimeEntry] = "True" Then...(where "FeeReviewAtTimeEntry" is a named range) which is referring to a workbook level name; how would i refer to a name on a worksheet other than the active one? would i need to refer to the 2 types of names differently? "Jack Schitt" wrote in message ... This also works: ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="MyRange", _ RefersToR1C1:="=Sheet1!R1C1" -- Return email address is not as DEEP as it appears "Jack Schitt" wrote in message ... I just turned on the macro recorder, created a local name that refers to Sheet1!$A$1, switched off the recorder and reviewed the code which showed: ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _ RefersToR1C1:= "=Sheet1!R1C1" So it seems that you just have to specify the worksheet name in front of the range name, in order to localise it to that sheet. -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... this i should know, but never get right... a range can be named at a workBOOK or workSHEET level (true?) ...this presumes that a name could either exist at the workbook level and have the same value on all of the worksheets or or the same name could exist on each worksheet and have a value specific to that sheet (is this correct?) i use this to define a name at the workbook level: ActiveWorkbook.Names.Add Name:="SuspendProgrammedAutomaticCalculation", _ RefersToR1C1:="=""True""" what would the correct code be for creating a worksheet level name thanks in advance mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges at workbook and worksheet levels
A bit out of my depth. If I get it wrong someone will pounce. But it seems
to me that if Sheet1 is the activesheet, and the range FeeReviewAtTimeEntry is local to sheet2, then If [Sheet2!FeeReviewAtTimeEntry] = "True" Then etc -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... currently i use this method: If [FeeReviewAtTimeEntry] = "True" Then...(where "FeeReviewAtTimeEntry" is a named range) which is referring to a workbook level name; how would i refer to a name on a worksheet other than the active one? would i need to refer to the 2 types of names differently? "Jack Schitt" wrote in message ... This also works: ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="MyRange", _ RefersToR1C1:="=Sheet1!R1C1" -- Return email address is not as DEEP as it appears "Jack Schitt" wrote in message ... I just turned on the macro recorder, created a local name that refers to Sheet1!$A$1, switched off the recorder and reviewed the code which showed: ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _ RefersToR1C1:= "=Sheet1!R1C1" So it seems that you just have to specify the worksheet name in front of the range name, in order to localise it to that sheet. -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... this i should know, but never get right... a range can be named at a workBOOK or workSHEET level (true?) ...this presumes that a name could either exist at the workbook level and have the same value on all of the worksheets or or the same name could exist on each worksheet and have a value specific to that sheet (is this correct?) i use this to define a name at the workbook level: ActiveWorkbook.Names.Add Name:="SuspendProgrammedAutomaticCalculation", _ RefersToR1C1:="=""True""" what would the correct code be for creating a worksheet level name thanks in advance mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges at workbook and worksheet levels
thanks...
(a wise old fool once said: "check the obvious first..."; how obvious...) "Jack Schitt" wrote in message ... A bit out of my depth. If I get it wrong someone will pounce. But it seems to me that if Sheet1 is the activesheet, and the range FeeReviewAtTimeEntry is local to sheet2, then If [Sheet2!FeeReviewAtTimeEntry] = "True" Then etc -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... currently i use this method: If [FeeReviewAtTimeEntry] = "True" Then...(where "FeeReviewAtTimeEntry" is a named range) which is referring to a workbook level name; how would i refer to a name on a worksheet other than the active one? would i need to refer to the 2 types of names differently? "Jack Schitt" wrote in message ... This also works: ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="MyRange", _ RefersToR1C1:="=Sheet1!R1C1" -- Return email address is not as DEEP as it appears "Jack Schitt" wrote in message ... I just turned on the macro recorder, created a local name that refers to Sheet1!$A$1, switched off the recorder and reviewed the code which showed: ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", _ RefersToR1C1:= "=Sheet1!R1C1" So it seems that you just have to specify the worksheet name in front of the range name, in order to localise it to that sheet. -- Return email address is not as DEEP as it appears "mark kubicki" wrote in message ... this i should know, but never get right... a range can be named at a workBOOK or workSHEET level (true?) ...this presumes that a name could either exist at the workbook level and have the same value on all of the worksheets or or the same name could exist on each worksheet and have a value specific to that sheet (is this correct?) i use this to define a name at the workbook level: ActiveWorkbook.Names.Add Name:="SuspendProgrammedAutomaticCalculation", _ RefersToR1C1:="=""True""" what would the correct code be for creating a worksheet level name thanks in advance mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting all named ranges that have a workbook scope | Excel Discussion (Misc queries) | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
Transfering 100 named ranges to an another workbook | Excel Discussion (Misc queries) | |||
Phantom named ranges in a workbook? | Excel Discussion (Misc queries) | |||
Linking to named ranges in another workbook | Excel Discussion (Misc queries) |