Hi

With your first set of days appearing in cells B4:H4, and the subsequent
sets of day headings being on rows 16, 28 and 40.
In cell A1 enter the date of the start of the 4 week pay period e.g.
28/07/2007

In cell A4 enter
="W/C "&TEXT($A$1+(INT(ROW()/12)*7),"dd/mm/yy")
Copy this formula to cells A16, A28 and A40

This will ensure that each row of days has the appropriate W/C date
corresponding to the value you have entered in A1.

Copy the following event code and paste it into the Sheet module of the
relevant sheet from your workbook.
To do this, Mark the codeControl +C to copyRight click on your sheet
tabView CodeControl + V to paste the code into the sheet module.
Press Alt + F11 to return to your worksheet.

Copy your current (starting rota) to cells A5:H14

Now, enter the date again into cell A1, and as soon as you press Enter, the
rota will be copied down and adjusted.
Each time you change the value in cell A1, this will be repeated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim n As Long, m As Long, o As Long, i As Long
n = 5: m = 14: o = 17

For i = 1 To 3
Range(Cells(m, 1), Cells(m, 8)).Select
Application.CutCopyMode = False
Selection.Cut
Cells(n, 1).Select
Selection.Insert Shift:=xlDown

Range(Cells(n, 1), Cells(m, 8)).Copy Cells(o, 1)
n = n + 12: m = m + 12: o = o + 12
Next i
Range(Cells(m, 1), Cells(m, 8)).Select
Application.CutCopyMode = False
Selection.Cut
Cells(n, 1).Select
Selection.Insert Shift:=xlDown
Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub


--
Regards
Roger Govier



"gramps" wrote in message
...

-- I operate the 10 week rotating roster as shown below. I need to be
able
to perform 2 separate tasks the first of which is to enter a start date of
the week and then as each new week starts the names drop down 1 line and
the
bottom name then goes back to the top of the roster.

The pay period is a 4 week cycle and so what I would also like to be able
to
do is post each person their duties for the next 4 week cycle which would
automatically update when the new period's start date is entered

Saturday Sunday Monday Tuesday Wednesday Thursday Friday
Duncan Rest A2 C2 A2 Rest C1 C1
Barbara C1 Rest Rest C2 C2 C2 C2
Rachel N2 Rest D1 D1 D1 D1 Rest
Latiffe A1 A1 C1 C1 C1 Rest Rest
Ali Rest Rest N2 N2 N2 N2 N2
Tony Rest C1 N1 N1 N1 Rest D1
Jorge N1 N1 Rest Rest A2 A2 A2
John A2 Rest NW NW Rest NW NW
Bryan Rest Rest A1 A1 A1 A1 A1
George Rest N2 A2 Rest NW N1 N1

Any help would be greatly appreciated.
Al