Modify dates byr referencing a cell name
 

Here is the situation:

I have 52 sheets each named by week, ie: May 5-May 11; May 12-May 18 etc.
I have a cell in each sheet that references the beginning date of each
sheet. Ie May 5; May 12 etc
As I am preparing 52 sheets I would prefer not to have to fill in the date
in each cell, in each sheet, or formulate by referencing the end date in
the previos sheet and adding one. I would like to have a macro do it, but
haven't much success so far. Of course I would have done manually by now
for the time taken to figure this out. :(
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Modify dates byr referencing a cell name
 

Robin,

Sub TryNow()
Dim myS As Worksheet
Dim myDate As Date
myDate = DateValue("5/5/2008")
For Each myS In Worksheets
myS.Range("A1").Value = myDate
myDate = myDate + 7
Next myS
End Sub

Of course, change the Starting date and the cell in which you write the date.

Note that what you are doing is typical of poor workbook design. Much better would be to use a
database, with a column of dates along with the other values. Then you could slice and dice your
data using Pivot Tables and filters, without having 52 sheets......

HTH,
Bernie
MS Excel MVP


"Robin" wrote in message ...
Here is the situation:

I have 52 sheets each named by week, ie: May 5-May 11; May 12-May 18 etc.
I have a cell in each sheet that references the beginning date of each sheet. Ie May 5; May 12
etc
As I am preparing 52 sheets I would prefer not to have to fill in the date in each cell, in each
sheet, or formulate by referencing the end date in the previos sheet and adding one. I would like
to have a macro do it, but haven't much success so far. Of course I would have done manually by
now for the time taken to figure this out. :(
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Modify dates byr referencing a cell name
 

Thanks! I only wish I knew databases!


On Wed, 30 Apr 2008 11:42:40 -0600, Bernie Deitrick <deitbe consumer dot
org wrote:

Robin,

Sub TryNow()
Dim myS As Worksheet
Dim myDate As Date
myDate = DateValue("5/5/2008")
For Each myS In Worksheets
myS.Range("A1").Value = myDate
myDate = myDate + 7
Next myS
End Sub

Of course, change the Starting date and the cell in which you write the
date.

Note that what you are doing is typical of poor workbook design. Much
better would be to use a
database, with a column of dates along with the other values. Then you
could slice and dice your
data using Pivot Tables and filters, without having 52 sheets......

HTH,
Bernie
MS Excel MVP


"Robin" wrote in message
...
Here is the situation:

I have 52 sheets each named by week, ie: May 5-May 11; May 12-May 18
etc.
I have a cell in each sheet that references the beginning date of each
sheet. Ie May 5; May 12
etc
As I am preparing 52 sheets I would prefer not to have to fill in the
date in each cell, in each
sheet, or formulate by referencing the end date in the previos sheet
and adding one. I would like
to have a macro do it, but haven't much success so far. Of course I
would have done manually by
now for the time taken to figure this out. :(
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/





--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Modify dates byr referencing a cell name
 

Thanks! I only wish I knew databases!

Well, here's a good opportunity to give it a try...

Bernie