Change Roster values

Hi Guru jaslake

It all make sense to me except for the cleaning roster part. I played around with it, adding columns adjusting it accordingly but no luck. Can you help me adjust cleaning roster sheet to have the following headers:

Date / Start / Finish/ Location / Employee / Employee / Yes or No / Shift / Posted Roster

            Case "CleaningRoster"

                'CleaningRoster follows the same logic as above but the Heading Layout
                'is different so requires different handling.  If it does not make sense
                'let me know
               Set rng = .Range("A3:A" & LR)
                For Each cel In rng
                    If cel.Offset(0, 4).Value <> "X" Then
                        sYear = Year(cel.Offset(0, 2).Value)
                        sMonth = WorksheetFunction.Text(cel.Offset(0, 2).Text, "mmm")
                        With ws1
                            Set myRng = .Range(sMonth & "_" & sYear)
                            myName = cel.Value
                            myName2 = cel.Offset(0, 1).Value
                            myDate = Format(cel.Offset(0, 2).Value, "dd/mm/yy")
                            Set myCol = myRng.Rows(1).Find _
                                    (what:=DateValue(myDate), LookIn:=xlFormulas)

                            On Error Resume Next
                            Set myRow = .Columns(1).Find(what:=myName, After:=.Cells(myRng.Rows(1).Row, 1), LookIn:=xlValues, LookAt:= _
                                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                                    , SearchFormat:=False)
                            On Error GoTo 0

                            On Error Resume Next
                            Set myRow2 = .Columns(1).Find(what:=myName2, After:=.Cells(myRng.Rows(1).Row, 1), LookIn:=xlValues, LookAt:= _
                                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                                    , SearchFormat:=False)
                            On Error GoTo 0
                            Intersect(myRow.EntireRow, myCol.EntireColumn) = cel.Offset(0, 3).Value
                            On Error Resume Next
                            Intersect(myRow2.EntireRow, myCol.EntireColumn) = cel.Offset(0, 3).Value
                            On Error GoTo 0
                            cel.Offset(0, 4).Value = "X"
                        End With
                    End If
                Next cel
            End Select
        End With
    Next vWks
End Sub

Secondly, I was checking the OTSummary sheet code, the "reason" column adds it to the roster. This is not correct because then 2 employees will be OT when suppose to be one. The employee that works the OT is in 1-2 column.

Hi Bloodywog
Sorry...didn't get an email on your edited post #37. One apparently does NOT get notifications on edits. Are Start and Finish times of the day?

Hi Bloodywog
The code in the attached has been modified for this in Cleaning Roster

Date / Start / Finish/ Location / Employee / Employee / Yes or No / Shift / Posted Roster

and this in Overtime

The employee that works the OT is in 1-2 column.

I ran the procedures several times and all appears well to me. As before, you'll need to modify Date Formats as required. Let me know of issues.