| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates-Automatically entered in cells | Excel Discussion (Misc queries) | |||
| Dates not entered correctly | Excel Discussion (Misc queries) | |||
| Dates entered for document returnx | Excel Discussion (Misc queries) | |||
| Validating Dates Entered | Excel Discussion (Misc queries) | |||
| If both dates are entered into a cell then answer is yes | Excel Worksheet Functions | |||
Linear Mode
