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.

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.

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.

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.

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.

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.

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.

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