Thank you so much - I'll look first thing after school/nursery run - it's 003" here. I've just had a look at the code and it looks like that may just do it. Thanks so much - will look and let yoh know


Quote Originally Posted by Philb1 View Post
Try this & see if it's what you're after

It only works up to Employee7 because that's all there is on the 4WeeklyOverallTemplate worksheet.
There's no buttons to press as it's a worksheet change macro & will trigger when something changes in columns D, E or F.
Adjust anything in columns D,E or F on EmployeeInformation worksheet and the macro will enter the new data on 4WeeklyOverallTemplate worksheet.
The dropbox link is for you to get the file. I can't upload any attachments to this site & haven't been able to for months.
To see the code right click EmployeeInformation worksheet tab & click view code
Cheers

https://www.dropbox.com/s/1842tyxiww...8472.xlsm?dl=0

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo TheExit

Dim SessionDayRng As Range, AmPmRng As Range, SessionNameRng As Range
Dim TplDayRange As Range, rCell As Range
Dim sEmployeeName1 As String, sSessionDay As String, sAMPM As String, sSessionName As String
Dim Lrow As Long, A As Long, RowNum As Long, Luc As Long

'   Find last used row in column 1 Sheet1
    Lrow = Me.Columns(1).Find(What:="*", LookIn:=xlValues, _
            LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlPrevious, _
            MatchCase:=False, searchformat:=False).Row

'   Find last used column in row 2 on Sheet2
    Luc = Sheet2.Rows(2).Find(What:="*", LookIn:=xlValues, _
            LookAt:=xlPart, Searchorder:=xlByColumns, searchdirection:=xlPrevious, _
            MatchCase:=False, searchformat:=False).Column

'   Set the 3 ranges to detect changes in
    Set SessionNameRng = Me.Range("D3:D" & Lrow)
    Set SessionDayRng = Me.Range("E3:E" & Lrow)
    Set AmPmRng = Me.Range("F3:F" & Lrow)

'Stop

If Not Intersect(Target, SessionDayRng) Is Nothing Or _
    Not Intersect(Target, SessionNameRng) Is Nothing Or _
    Not Intersect(Target, AmPmRng) Is Nothing Then

    Application.EnableEvents = False

'   Populate variables
    sEmployeeName1 = Me.Cells(Target.Row, 1)
    sSessionName = Me.Cells(Target.Row, 4)
    sSessionDay = Me.Cells(Target.Row, 5)
    sAMPM = Me.Cells(Target.Row, 6)

'   Find row number employee is on in Sheet2
    A = Sheet2.Columns(2).Find(What:=sEmployeeName1, LookIn:=xlValues, _
            LookAt:=xlWhole, Searchorder:=xlByRows, searchdirection:=xlNext, _
            MatchCase:=False, searchformat:=False).Row
    
'   Set day range on Sheet2 (4WeeklyOverallTemplate)
    Set TplDayRange = Sheet2.Range(Sheet2.Cells(2, 4), Sheet2.Cells(2, Luc))

'   Select the correct row for entering details in
    Select Case UCase(sAMPM)

        Case Is = "AM": RowNum = A

        Case Is = "PM": RowNum = A + 1

        Case Is = "NIGHT": RowNum = A + 2

        Case Is = "CALL": RowNum = A + 3

    End Select

'   Delete any previous meetings etc of the employee
    Sheet2.Range(Sheet2.Cells(A, 4), Sheet2.Cells(A + 3, Luc)).ClearContents

'   Enter the employees new or altered data on Sheet1 (EmployeeInformation)
'   on to sheet 2 (4WeeklyOverallTemplate)
    For Each rCell In TplDayRange
        If UCase(rCell) = UCase(sSessionDay) Then Sheet2.Cells(RowNum, rCell.Column) = sSessionName
    Next rCell

Else

    GoTo TheExit

End If

TheExit:
Application.EnableEvents = True
Exit Sub

End Sub