Modify dates byr referencing a cell name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin - 30 Apr 2008 17:22 GMT

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. :(

Signature

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

Reply to this Message

Bernie Deitrick - 30 Apr 2008 18:42 GMT

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

> Here is the situation:
>
[quoted text clipped - 5 lines]
> 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. :(

Reply to this Message

Robin - 30 Apr 2008 20:23 GMT

Thanks! I only wish I knew databases!

> Robin,
>
[quoted text clipped - 37 lines]
>> --
>> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Signature

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

Reply to this Message

Bernie Deitrick - 01 May 2008 13:42 GMT

> Thanks! I only wish I knew databases!

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

Bernie

Reply to this Message