Hi Al
For your 10 employee scenario
Initial setting
n = 5: m = 14: o = 17
each time through loop
n = n + 12: m = m + 12: o = o + 12

n is the starting row number, Employee 1
m is the last employee row number, Employee 10
o is the row number for the 1st Employee in the second week.

12 is the add-on each time (based upon 10 employees and 3 row interval 2
blank lines, 1 line of days)

so, for 5 employees change those 2 lines in the code to
n=5 : m=9 : o=12
n=n+7:m=m+7:o=o+7

--
Regards
Roger Govier



"gramps" wrote in message
...
Hi Roger
Sorry, I didn't explain what I meant properly. What I was wondering was
what
changes to make if it was only a 5 man rota but to still show 4 weeks at a
time.
Thanks again
--
Al
"Roger Govier" wrote:

Hi

Just change the size of the loop from 3 to 4
For i = 1 To 3

For i = 1 To 4
--
Regards
Roger Govier




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
Cells(m, 1).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
Cells(m, 1).Select
Application.CutCopyMode = False
Selection.Cut
Cells(n, 1).Select
Selection.Insert Shift:=xlDown
Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub


I hope this now resolves the problem.

--
Regards
Roger Govier