I put times in 15 minjute intervals in Column a starting on row 2. do do
this easily I used auto fill. the days of the week arre in columns B-G. the
program assume each person has same break at same time and works Monday -
Saturday.


Const Breaks = "Breaks"
Const Duties = "Duties"

Sheets(Breaks).Activate
'time is listed in 15 minute intervals in column A
'Starting in row 2
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set TimeRange = Range(Cells(2, 1), Cells(Lastrow, 1))


Sheets(Duties).Activate
Set BreakRange = Range(Cells(6, 8), Cells(31, 11))

For Each cell In BreakRange

If Not IsEmpty(cell) Then

For Each BreakTime In TimeRange

If cell.Value < _
BreakTime.Offset(rowoffset:=1, columnoffset:=0) Then

Sheets(Breaks).Activate
Set BreaksCells = Sheets(Breaks). _
Range("B" & BreakTime.Row & ":G" & BreakTime.Row)

For Each Break In BreaksCells
' Add names to Break sheet.
'More than one person can be on break at the same time
If IsEmpty(Break) Then
Break.Value = Break.Value + _
Sheets(Duties).Range("B" & cell.Row)
Else
Break.Value = Break.Value + ", " + _
Sheets(Duties).Range("B" & cell.Row)
End If
Next Break

Exit For
End If

Next BreakTime

End If

Next cell

End Sub

"DB" wrote:

I'm trying to put together a set of worksheets for work that show up who's
on a tea break at a given time..

The project has 3 tabs at the bottom to choose either
DUTIES,,SHIFTS,,BREAKS..

The first 2 DUTIES and SHIFTS work fine....but what i'm trying to do now is
get the BREAKS sheets to display the day in 15 minute segments and each 15
minutes display the people who are on a break at that time.

The DUTIES screen scrolls down to show each day of the week we are open (Mon
to Sat)

Row B has all the names of the staff and goes from cell 6 down to cell 31.

The breaks are shown in Time format in columns H to K and again in cells 6
to 31.

Is there a way that I can get the BREAKS sheets to check the times on the
DUTIES sheet and automatically insert them into the relevant time slots.


I can send the file if needed.

Many Thanks

Dave B