Excel VBA 3D Ranges
 

I'm writing a small program to generate some data each day of the month.
Each month is to be a sheet. So same cell locations, different sheets.
Unfortunately there's lots of cells so it would be nice to use 3D names.

But I am unable to figure out how to get VBA to select the specific cell on
the sheet for the specific month. I apparently have to name each cell in
each month differently and then, depending upon the month the data is
generated for, in the macro change the name of the range I wish to write to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a value (from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

Why not create a name of Cell1 for each worksheet, like so

ActiveWorkbook.Names.Add Name:="Sheet3!cell1",
RefersTo:="=Sheet3!R1C1"

and then just address it simply with

?worksheets("Sheet3").range("cell1").value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:6s32c.711709$ts4.452565@pd7tw3no...
I'm writing a small program to generate some data each day of the month.
Each month is to be a sheet. So same cell locations, different sheets.
Unfortunately there's lots of cells so it would be nice to use 3D names.

But I am unable to figure out how to get VBA to select the specific cell
on
the sheet for the specific month. I apparently have to name each cell in
each month differently and then, depending upon the month the data is
generated for, in the macro change the name of the range I wish to write
to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a value (from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

Perhaps I'm missing something from your reply but like I said: There is a
lot of cells! So it would be nice to reduce the naming workload by about
1/12.

"Bob Phillips" wrote in message
...
Why not create a name of Cell1 for each worksheet, like so

ActiveWorkbook.Names.Add Name:="Sheet3!cell1",
RefersTo:="=Sheet3!R1C1"

and then just address it simply with

?worksheets("Sheet3").range("cell1").value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:6s32c.711709$ts4.452565@pd7tw3no...
I'm writing a small program to generate some data each day of the month.
Each month is to be a sheet. So same cell locations, different sheets.
Unfortunately there's lots of cells so it would be nice to use 3D names.

But I am unable to figure out how to get VBA to select the specific cell
on
the sheet for the specific month. I apparently have to name each cell
in
each month differently and then, depending upon the month the data is
generated for, in the macro change the name of the range I wish to write
to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a value
(from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

You can with a loop like so

For Each sh In ACtiveworkbook.Worksheets
sh.Range("A1").Name = "'" & sh.Name & "'!cell1"
sh.Range("A2").Name = "'" & sh.Name & "'!cell2"
'etc
Next sh

and you still have the simple referencing I metioned.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:lV32c.711957$ts4.624776@pd7tw3no...
Perhaps I'm missing something from your reply but like I said: There is a
lot of cells! So it would be nice to reduce the naming workload by about
1/12.

"Bob Phillips" wrote in message
...
Why not create a name of Cell1 for each worksheet, like so

ActiveWorkbook.Names.Add Name:="Sheet3!cell1",
RefersTo:="=Sheet3!R1C1"

and then just address it simply with

?worksheets("Sheet3").range("cell1").value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:6s32c.711709$ts4.452565@pd7tw3no...
I'm writing a small program to generate some data each day of the
month.
Each month is to be a sheet. So same cell locations, different
sheets.
Unfortunately there's lots of cells so it would be nice to use 3D
names.

But I am unable to figure out how to get VBA to select the specific
cell
on
the sheet for the specific month. I apparently have to name each cell
in
each month differently and then, depending upon the month the data is
generated for, in the macro change the name of the range I wish to
write
to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a value
(from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

OK, got you! Shoulda thought of that myself.
Thanks,
Perry.


"Bob Phillips" wrote in message
...
You can with a loop like so

For Each sh In ACtiveworkbook.Worksheets
sh.Range("A1").Name = "'" & sh.Name & "'!cell1"
sh.Range("A2").Name = "'" & sh.Name & "'!cell2"
'etc
Next sh

and you still have the simple referencing I metioned.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:lV32c.711957$ts4.624776@pd7tw3no...
Perhaps I'm missing something from your reply but like I said: There is
a
lot of cells! So it would be nice to reduce the naming workload by
about
1/12.

"Bob Phillips" wrote in message
...
Why not create a name of Cell1 for each worksheet, like so

ActiveWorkbook.Names.Add Name:="Sheet3!cell1",
RefersTo:="=Sheet3!R1C1"

and then just address it simply with

?worksheets("Sheet3").range("cell1").value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:6s32c.711709$ts4.452565@pd7tw3no...
I'm writing a small program to generate some data each day of the
month.
Each month is to be a sheet. So same cell locations, different
sheets.
Unfortunately there's lots of cells so it would be nice to use 3D
names.

But I am unable to figure out how to get VBA to select the specific
cell
on
the sheet for the specific month. I apparently have to name each
cell
in
each month differently and then, depending upon the month the data
is
generated for, in the macro change the name of the range I wish to
write
to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a value
(from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to
help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

If you think of it, what are we going to do<vbg

Bob

"!p^&c88%B!" wrote in message
news:rZ42c.709852$X%5.307964@pd7tw2no...
OK, got you! Shoulda thought of that myself.
Thanks,
Perry.


"Bob Phillips" wrote in message
...
You can with a loop like so

For Each sh In ACtiveworkbook.Worksheets
sh.Range("A1").Name = "'" & sh.Name & "'!cell1"
sh.Range("A2").Name = "'" & sh.Name & "'!cell2"
'etc
Next sh

and you still have the simple referencing I metioned.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:lV32c.711957$ts4.624776@pd7tw3no...
Perhaps I'm missing something from your reply but like I said: There
is
a
lot of cells! So it would be nice to reduce the naming workload by
about
1/12.

"Bob Phillips" wrote in message
...
Why not create a name of Cell1 for each worksheet, like so

ActiveWorkbook.Names.Add Name:="Sheet3!cell1",
RefersTo:="=Sheet3!R1C1"

and then just address it simply with

?worksheets("Sheet3").range("cell1").value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:6s32c.711709$ts4.452565@pd7tw3no...
I'm writing a small program to generate some data each day of the
month.
Each month is to be a sheet. So same cell locations, different
sheets.
Unfortunately there's lots of cells so it would be nice to use 3D
names.

But I am unable to figure out how to get VBA to select the
specific
cell
on
the sheet for the specific month. I apparently have to name each
cell
in
each month differently and then, depending upon the month the data
is
generated for, in the macro change the name of the range I wish to
write
to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a
value
(from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to
help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

Get fired? Lose your princely salary? <g

Regards,

Vasant.

"Bob Phillips" wrote in message
...
If you think of it, what are we going to do<vbg

Bob

"!p^&c88%B!" wrote in message
news:rZ42c.709852$X%5.307964@pd7tw2no...
OK, got you! Shoulda thought of that myself.
Thanks,
Perry.


"Bob Phillips" wrote in message
...
You can with a loop like so

For Each sh In ACtiveworkbook.Worksheets
sh.Range("A1").Name = "'" & sh.Name & "'!cell1"
sh.Range("A2").Name = "'" & sh.Name & "'!cell2"
'etc
Next sh

and you still have the simple referencing I metioned.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:lV32c.711957$ts4.624776@pd7tw3no...
Perhaps I'm missing something from your reply but like I said:
There
is
a
lot of cells! So it would be nice to reduce the naming workload by
about
1/12.

"Bob Phillips" wrote in message
...
Why not create a name of Cell1 for each worksheet, like so

ActiveWorkbook.Names.Add Name:="Sheet3!cell1",
RefersTo:="=Sheet3!R1C1"

and then just address it simply with

?worksheets("Sheet3").range("cell1").value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"!p^&c88%B!" wrote in message
news:6s32c.711709$ts4.452565@pd7tw3no...
I'm writing a small program to generate some data each day of
the
month.
Each month is to be a sheet. So same cell locations, different
sheets.
Unfortunately there's lots of cells so it would be nice to use
3D
names.

But I am unable to figure out how to get VBA to select the
specific
cell
on
the sheet for the specific month. I apparently have to name
each
cell
in
each month differently and then, depending upon the month the
data
is
generated for, in the macro change the name of the range I wish
to
write
to.
This rather awkward and not very elegant (to say the least!).

So the problem is: I can insert a name "Cell1" that refers to
January:December!$A$1 (for instance) but how then do I write a
value
(from
the macro) to the "Cell1" in worksheet "April"?

I'm sure it's something easy but I cannot find any references to
help.

Thanks in Advance,
Perry.

Excel VBA 3D Ranges
 

Damn no! I rely on that<G

Bob

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Get fired? Lose your princely salary? <g

Regards,

Vasant.

"Bob Phillips" wrote in message
...
If you think of it, what are we going to do<vbg

Bob