looking for a simple solution

[colinheslop1984]

I am building a rota and so have 52 work sheets titled week1, week2, etc...

each sheet is identical in layout, similar to below. Assume that cells here run from A1:O5

What I want to do is have all the dates update across the 52 sheets when I enter the 'start' date in week1

Currently I am just doing by d1=b1+1, f1=d1+1 and so on, then starting week2 with b1='week1'!n1+1 and so on but this is so time consuming.

Is there a simpler way?

02-jun

hrs worked

03-jun

hrs worked

04-jun

hrs worked

05-jun

hrs worked

06-jun

hrs worked

07-jun

hrs worked

08-jun

hrs worked

name

9-6

8

11-9

8

DO

DO

9-6

8

9-6

8

9-6

8

name

name

name

<tbody>
</tbody>

 

[NoSparks]

This can be done with an event macro.
Right click on the Week1 sheet tab and select View Code.
Copy and paste this into the pane on the right.

Private Sub Worksheet_Change(ByVal Target As Range)
' limit to single cell
If Target.Count > 1 Then Exit Sub
' limit to specific cell
If Target.Address <> "$B$1" Then Exit Sub
' must be a date
If Not IsDate(Target.Value) Then Exit Sub

' Good to go
Dim wkNum As Long, theDay As Long, Dt As Date
    
Dt = Target.Value

Application.EnableEvents = False
Application.ScreenUpdating = False

For wkNum = 1 To 52
    For theDay = 1 To 7
        Sheets("Week" & wkNum).Cells(1, theDay * 2) = Format(Dt, "dd-mmm")
        Dt = Dt + 1
    Next theDay
Next wkNum
        
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

now when you change the Week1 B1 date all the dates should update.