vba "evaluate" code not working

Hello, I am making a logbook, where I enter flight details in the AllData sheet, and when I select "ok" in column L, the code should automatically copy the information to the pilots sheets and make some adjustments.
The code I wrote with the help of the members of this forum works good for this part.
Now if I select CLR in AllData sheet, let's say in row 6, the code should go to the pilot sheet (pilot3) and check which row has the same information as this one. (in this case it is "row 5" in "pilot3" )
Then it should send a message box with the value of the row number.
But the code is not working for this part, it always returns a message box with the number 0...I think the error is in the " (destRow3 = Evaluate...) part " perhaps there is something wrong or something missing, and I am not able to continue, so your help is much appreciated, thanks.
This is the code I used, and I uploaded a sample of the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myDestSheet As Worksheet, reply As String
    Dim myDestSheet2 As Worksheet, destRow As Long, destRow2 As Long
    Dim myDestSheet3 As Worksheet, destRow3 As Long
    With ThisWorkbook
        If Target.Cells.Count = 1 And Target.Column = 12 And Target = "ok" Then
            On Error Resume Next
            Set myDestSheet = Worksheets(Target.Offset(, -11).Value)
            
            If Target.Value = "ok" Then
                reply = MsgBox("Are you sure about the information?" & vbNewLine & "Press YES to confirm" & vbNewLine & "Or Press NO to cancel and edit again", vbYesNo)
            End If
            
            If reply = vbYes Then
            
            If Err.Number = 9 Then
            On Error Resume Next
            Set myDestSheet2 = Worksheets(Target.Offset(, -10).Value)

                If Err.Number = 9 Then
                On Error Resume Next
                MsgBox "Sheet " & Target.Offset(, -11).Value & Chr(10) & " Does Not Exist " & Chr(10) & " NO DATA WAS ADDED "
                Err.Clear
                    MsgBox "Sheet " & Target.Offset(, -10).Value & Chr(10) & " Does Not Exist " & Chr(10) & " NO DATA WAS ADDED "
                    Err.Clear

                
                Else
                    destRow2 = myDestSheet2.Cells(Rows.Count, "a").End(xlUp).Row + 1
                    Application.EnableEvents = False
                    Target.Parent.Cells(Target.Row, 5).Resize(, 7).Copy .Sheets(myDestSheet2.Name).Cells(destRow2, 5)
                    With myDestSheet2
                        .Cells(destRow2, 1) = Target.Offset(, -9)
                        .Cells(destRow2, 2) = Target.Offset(, -8)
                        .Cells(destRow2, 3) = "SIC"
                        .Cells(destRow2, 4) = Target.Offset(, -11)
                    End With
                    Application.EnableEvents = True
                    MsgBox "Sheet " & Target.Offset(, -11).Value & Chr(10) & " Does Not Exist"
                Err.Clear
MsgBox " Data was added to Sheet " & Target.Offset(, -10).Value
Err.Clear
                End If
                    Else
                destRow = myDestSheet.Cells(Rows.Count, "a").End(xlUp).Row + 1
                Set myDestSheet2 = Worksheets(Target.Offset(, -10).Value)

                If Err.Number = 9 Then
                On Error Resume Next
                    Application.EnableEvents = False
                    Target.Parent.Cells(Target.Row, 5).Resize(, 7).Copy .Sheets(myDestSheet.Name).Cells(destRow, 5)
                    With myDestSheet
                        .Cells(destRow, 1) = Target.Offset(, -9)
                        .Cells(destRow, 2) = Target.Offset(, -8)
                        .Cells(destRow, 3) = "PIC"
                        .Cells(destRow, 4) = Target.Offset(, -10)
                    End With
                    Application.EnableEvents = True
                    MsgBox "Sheet " & Target.Offset(, -10).Value & Chr(10) & " Does Not Exist"
                    Err.Clear
MsgBox " Data was added to Sheet " & Target.Offset(, -11).Value
Err.Clear
                    Else
                    destRow2 = myDestSheet2.Cells(Rows.Count, "a").End(xlUp).Row + 1
                    Application.EnableEvents = False
                    Target.Parent.Cells(Target.Row, 5).Resize(, 7).Copy .Sheets(myDestSheet.Name).Cells(destRow, 5)
                    Target.Parent.Cells(Target.Row, 5).Resize(, 7).Copy .Sheets(myDestSheet2.Name).Cells(destRow2, 5)
                    With myDestSheet
                        .Cells(destRow, 1) = Target.Offset(, -9)
                        .Cells(destRow, 2) = Target.Offset(, -8)
                        .Cells(destRow, 3) = "PIC"
                        .Cells(destRow, 4) = Target.Offset(, -10)
                    End With
                    With myDestSheet2
                        .Cells(destRow2, 1) = Target.Offset(, -9)
                        .Cells(destRow2, 2) = Target.Offset(, -8)
                        .Cells(destRow2, 3) = "SIC"
                        .Cells(destRow2, 4) = Target.Offset(, -11)
                    End With
                    Application.EnableEvents = True
                End If
                End If
                
                 Else
                       Target.Value = ""
                
                 End If
        Else
       If Target.Cells.Count = 1 And Target.Column = 12 And Target = "CLR" Then
            Set myDestSheet3 = Worksheets(Target.Offset(, -11).Value)
destRow3 = Evaluate("=SUMPRODUCT(--('" & myDestSheet3.Name & "'!A3:A10007=""" & Target.Parent.Cells(Target.Row, 3).Value & """),--('" & myDestSheet3.Name & "'!B3:B10007=""" & Target.Parent.Cells(Target.Row, 4).Value & """),--('" & myDestSheet3.Name & "'!d3:d10007=""" & Target.Parent.Cells(Target.Row, 2).Value & """),ROW(d3:d10007))")
Application.EnableEvents = False
With myDestSheet3
Rows(destRow3 & ":" & destRow3).Select
Selection.ClearContents
 End With
 Application.EnableEvents = True

 End If
End If
        
    End With
End Sub