Need help with 52 worksheets that need dates entered
 

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?

Need help with 52 worksheets that need dates entered
 

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" wrote:

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?

Need help with 52 worksheets that need dates entered
 

Thank you so much, worked perfectly!!

"Mike H" wrote:

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" wrote:

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?