How to move row to another sheet?

I created a spreadsheet, with multiple tabs.

This moves rows to corresponding sheets based on a drop down selection:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    If Target.Column = 3 And Target.Row > 7 And (Target.Value = "LTS" Or Target.Value = "On Hold") Then
        Application.EnableEvents = False
'       Copy columns A to AU to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("On Hold and LTS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    If Target.Column = 3 And Target.Row > 7 And (Target.Value = "Leaver") Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("Leavers").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

Although the code works as intended, every time I receive

run time error 424 - object required message

Clicking end resolves it.

The second part of the code [ Leaver ] does not produce the error, and also works as intended.

How could I stop this error from coming up?

Solution

If the cell contains "LTS" (or "On Hold") then the first If..End If block runs and among other things deletes the row containing Target. You then move to your second If..End If block and test Target - which no longer exists - hence your error.

Try using ElseIf in there instead:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    If Target.Column = 3 And Target.Row > 7 And (Target.Value = "LTS" Or Target.Value = "On Hold") Then

        Application.EnableEvents = False
'       Copy columns A to AU to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("On Hold and LTS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    ElseIf Target.Column = 3 And Target.Row > 7 And (Target.Value = "Leaver") Then

        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("Leavers").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub