Excel VBA 3D Ranges

  • Thread starter Thread starter !p^&c88%B!
  • Start date Start date
P

!p^&c88%B!

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)
 
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.
 
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)
 
Back
Top