Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 15th 04, 09:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2003
Posts: 89
Default 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   Report Post  
Old September 15th 04, 09:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2004
Posts: 63
Default 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   Report Post  
Old September 15th 04, 09:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2004
Posts: 63
Default 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   Report Post  
Old September 15th 04, 10:30 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2003
Posts: 89
Default 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   Report Post  
Old September 15th 04, 10:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2004
Posts: 63
Default 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   Report Post  
Old September 15th 04, 10:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2003
Posts: 89
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting all named ranges that have a workbook scope Babymech Excel Discussion (Misc queries) 3 February 19th 09 04:21 PM
Copy worksheet with named ranges to new workbook and keep names Sandy Excel Worksheet Functions 0 July 11th 08 04:37 PM
Transfering 100 named ranges to an another workbook Oldjay Excel Discussion (Misc queries) 2 August 8th 07 07:40 PM
Phantom named ranges in a workbook? Dave O Excel Discussion (Misc queries) 3 September 26th 06 05:26 PM
Linking to named ranges in another workbook KG Excel Discussion (Misc queries) 4 March 30th 06 03:07 AM


All times are GMT +1. The time now is 01:22 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017