On cell change, submit data to another sheet

I have a sheet called "Control" where I want to put the date in cell "M12" & when I move out of cell it copies that date to the cell on sheet "Escalation Rotation" beside the name of the associate that was beside "M12" in "Control". I have included my workbook so you can see what needs to be done. Any help would be greatly appricaited!!!

Here's a worksheet procedure for "Control". The macro runs when the value in cell "M12" is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("M12")) Is Nothing Then Exit Sub
    
    If IsDate(Target.Value) Then
        Dim found As Range
        Dim firstFound As Range
        
        With Sheets("Escalation Rotation").Range("A:A")
            Set found = .Find(What:=Range("I12"), LookAt:=xlWhole)
            If Not found Is Nothing Then
                found.Offset(, 1) = Target.Value
                Set firstFound = found
                Do
                    Set found = .FindNext(found)
                    If Not found Is Nothing Then
                        found.Offset(, 1) = Target.Value
                    End If
                Loop Until found.Address = firstFound.Address
            End If
        End With
    End If
End Sub

Originally Posted by yujin

Here's a worksheet procedure for "Control". The macro runs when the value in cell "M12" is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("M12")) Is Nothing Then Exit Sub
    
    If IsDate(Target.Value) Then
        Dim found As Range
        Dim firstFound As Range
        
        With Sheets("Escalation Rotation").Range("A:A")
            Set found = .Find(What:=Range("I12"), LookAt:=xlWhole)
            If Not found Is Nothing Then
                found.Offset(, 1) = Target.Value
                Set firstFound = found
                Do
                    Set found = .FindNext(found)
                    If Not found Is Nothing Then
                        found.Offset(, 1) = Target.Value
                    End If
                Loop Until found.Address = firstFound.Address
            End If
        End With
    End If
End Sub

Would I be able to set the range from M12 to M16?

Would I be able to set the range from M12 to M16?

OK, voila!

Private Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Range("M12:M16")) Is Nothing Then Exit Sub
    
    If IsDate(target.Value) Then
        Dim found As Range
        Dim firstFound As Range
        
        With Sheets("Escalation Rotation").Range("A:A")
            Set found = .Find(What:=target.Offset(, -4).Value, LookAt:=xlWhole)
            If Not found Is Nothing Then
                found.Offset(, 1) = target.Value
                Set firstFound = found
                Do
                    Set found = .FindNext(found)
                    If Not found Is Nothing Then
                        found.Offset(, 1) = target.Value
                    End If
                Loop Until found.Address = firstFound.Address
            End If
        End With
    End If
End Sub

Ok so this does similar to what I need! When it copies the date over to "Escalation Rotation" sheet, it copies the date all the way down, I need it to find the 1st blank cell and put that date in. Then once it is copies over, I need the contents in cells "M12:M16" cleared ready for the next date that is put in for a different associate! Thank you for your help!

Here I have modified the code. Try it.

Private Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Range("M12:M16")) Is Nothing Then Exit Sub
    
    If IsDate(target.Value) Then
        Dim found As Range
        Dim firstFound As Range
        
        With Sheets("Escalation Rotation").Range("A:A")
            Set found = .Find(What:=target.Offset(, -4).Value, LookAt:=xlWhole)
            If Not found Is Nothing Then
                If IsEmpty(found.Offset(, 1)) Then
                    found.Offset(, 1) = target.Value
                Else
                    Set firstFound = found
                    Do
                        Set found = .FindNext(found)
                        If Not found Is Nothing Then
                            If IsEmpty(found.Offset(, 1)) Then
                                found.Offset(, 1) = target.Value
                                Exit Do
                            End If
                        End If
                    Loop Until found.Address = firstFound.Address
                End If
            End If
        End With
        
        Range("M12:N16").ClearContents
        Range("M12:N12").Select
    End If
End Sub