See if the code below does the job for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestSheet As String
If Intersect(Target, Range("F:F,I:I,L:L,O:O,R:R")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
DestSheet = "Other Sheet"
If Target.Offset(0, 1) = vbNullString Then
Target.Value = vbNullString
MsgBox "Enter Start Date before Employee Name."
Target.Copy Sheets(DestSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Cells(2, Target.Column).Copy Sheets(DestSheet).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Target.Offset(0, 1).Copy Sheets(DestSheet).Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
To use the code right-click the Master sheets tab and select View Code. Paste the code in the big white field.
Things you need/want to change:
- On the forth code line you will find the column letters this code applies to. Add to this list to match your column department headers.
- On the eighth code line you will find the name of the destination sheet, which you called Other Sheet in your query, so that is the name I went with. Change if needed.
And one more thing, the code requires you to enter the Start Date before the Employee Name. When you don't, the employee name is cleared and a message appears to remind you of this.