HOW TO: Name Range?
 

Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

Hi Kevin,

Try something like:

'=============
Public Sub Tester001()
Dim SH As Worksheet
Const sAdd As String = "A1:A10"

For Each SH In ActiveWorkbook.Worksheets
Names.Add Name:=SH.Name & "!Data", _
RefersTo:=SH.Range(sAdd)
Next SH

End Sub
'<<=============


---
Regards,
Norman


"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

OK,

I click on Insert | Name | Define, I type in the Names in workbook, MYDATA,
in the Refers to box I put =!A2:B10 click on Add then OK, but nothing shows
up in the Name Range List, what am I doing wrong, please help.

TIA
KM

"Bob Umlas" wrote:

Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

All named ranges don't show up in the name dropdown, but will appear in
Insert=Names=Define. This includes names that are variable in nature such
as this one.

In the name box, type in MyData and hit return. does it select the proper
range?

--
Regards
Tom Ogilvy

"Kevin McCartney" wrote in
message ...
OK,

I click on Insert | Name | Define, I type in the Names in workbook,
MYDATA,
in the Refers to box I put =!A2:B10 click on Add then OK, but nothing
shows
up in the Name Range List, what am I doing wrong, please help.

TIA
KM

"Bob Umlas" wrote:

Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

Unfortunately not, the Refers to for some reason changes, and it seems to
select the 10 cells below the active cell when I type in the Name dropdown,
not sure what I'm doing wrong.

TIA
KM

"Tom Ogilvy" wrote:

All named ranges don't show up in the name dropdown, but will appear in
Insert=Names=Define. This includes names that are variable in nature such
as this one.

In the name box, type in MyData and hit return. does it select the proper
range?

--
Regards
Tom Ogilvy

"Kevin McCartney" wrote in
message ...
OK,

I click on Insert | Name | Define, I type in the Names in workbook,
MYDATA,
in the Refers to box I put =!A2:B10 click on Add then OK, but nothing
shows
up in the Name Range List, what am I doing wrong, please help.

TIA
KM

"Bob Umlas" wrote:

Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

But that's because I need to put !A$2$:B$10$, thanks all
:-)

regards
KM

"Kevin McCartney" wrote:

Unfortunately not, the Refers to for some reason changes, and it seems to
select the 10 cells below the active cell when I type in the Name dropdown,
not sure what I'm doing wrong.

TIA
KM

"Tom Ogilvy" wrote:

All named ranges don't show up in the name dropdown, but will appear in
Insert=Names=Define. This includes names that are variable in nature such
as this one.

In the name box, type in MyData and hit return. does it select the proper
range?

--
Regards
Tom Ogilvy

"Kevin McCartney" wrote in
message ...
OK,

I click on Insert | Name | Define, I type in the Names in workbook,
MYDATA,
in the Refers to box I put =!A2:B10 click on Add then OK, but nothing
shows
up in the Name Range List, what am I doing wrong, please help.

TIA
KM

"Bob Umlas" wrote:

Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

Yes, for a fixed range, you should use absolute addressing.

--
Regards,
Tom Ogilvy

"Kevin McCartney" wrote in
message ...
But that's because I need to put !A$2$:B$10$, thanks all
:-)

regards
KM

"Kevin McCartney" wrote:

Unfortunately not, the Refers to for some reason changes, and it seems
to
select the 10 cells below the active cell when I type in the Name
dropdown,
not sure what I'm doing wrong.

TIA
KM

"Tom Ogilvy" wrote:

All named ranges don't show up in the name dropdown, but will appear
in
Insert=Names=Define. This includes names that are variable in
nature such
as this one.

In the name box, type in MyData and hit return. does it select the
proper
range?

--
Regards
Tom Ogilvy

"Kevin McCartney" wrote in
message ...
OK,

I click on Insert | Name | Define, I type in the Names in workbook,
MYDATA,
in the Refers to box I put =!A2:B10 click on Add then OK, but
nothing
shows
up in the Name Range List, what am I doing wrong, please help.

TIA
KM

"Bob Umlas" wrote:

Define the name and have it referring to the range A2:B10 WITH A
LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote
in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I
set a
named
range called My Data and when I click on it, it selects the A2
to B10
depending on which sheet is active, how do I do it?


TIA
KM

HOW TO: Name Range?
 

Personally I recommend not using Names with refers-to starting with !
because there is an Excel bug that gives the wrong answer (always refers to
the active sheet) whenever calculation is called from VBA.

Its safer to use =INDIRECT("$A$2:$B$10") instead.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Bob Umlas" wrote in message
...
Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK:
MyData Refers to
!A2:B10
Now you have what I call a global/local name.
See page 49 of my book "This isn't Excel it's Magic!"
http://www.iil.com/iil/excelmagic

"Kevin McCartney" wrote in
message ...
Hi TWIMC,

I'd like to add one name range that works on each sheet. E.g. I set a
named
range called My Data and when I click on it, it selects the A2 to B10
depending on which sheet is active, how do I do it?


TIA
KM