Modify dates byr referencing a cell name

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

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. :(
 
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
 
Thanks! I only wish I knew databases!


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
 
Thanks! I only wish I knew databases!

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

Bernie
 
Back
Top