Need help with 52 worksheets that need dates entered

[Susan]

I have a date on the first sheet of 8/8/09 and i need to change the dates for
each additonal worksheet by adding 7 days, 8/15/09 on the 2nd, 8/22/09 on the
3rd etc.

Is there a macro i can use so i dont have to change all 52 worksheets just
the first one and it will change that cell in each additional sheet?

 

[Mike H]

Hi,

Right click the tab on your first sheet, view code and paste this in
Entering a date in A1 will change the date in all other sheets


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsDate(Target) Then
On Error Resume Next
Application.EnableEvents = False
Mydate = Target.Value
For x = 2 To 51
Mydate = Mydate + 7
Sheets(x).Range("A1") = Mydate
Next
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub


Mike

 

[Susan]

Thank you so much, worked perfectly!!

Hi,

Right click the tab on your first sheet, view code and paste this in
Entering a date in A1 will change the date in all other sheets


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsDate(Target) Then
On Error Resume Next
Application.EnableEvents = False
Mydate = Target.Value
For x = 2 To 51
Mydate = Mydate + 7
Sheets(x).Range("A1") = Mydate
Next
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub


Mike