Microsoft Office Forums - View Single Post

NoSparks · 11-01-2017, 05:13 AM

I suspect the 'Object Required' error is on the If Target.Column = 13 line and is happening because the If Target.Column =12 part has just deleted the target row,
so that line is looking for a target that no longer exists because its just been deleted.

Try this way

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nxtRow As Long

' limit to a single cell change in columns 12 or 13
If Target.Count > 1 Then Exit Sub
If Target.Column < 12 Or Target.Column > 13 Then Exit Sub

' if column 12
If Target.Column = 12 And UCase(Target.Value) = "YES" Then
    nxtRow = Sheets("Closed Issues").Range("L" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy _
    Destination:=Sheets("Closed Issues").Range("A" & nxtRow)
    Application.EnableEvents = False
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Exit Sub
End If

' if column 13
If Target.Column = 13 And UCase(Target.Value) = "LOW" Then
    nxtRow = Sheets("Low Priority Issues").Range("M" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy _
    Destination:=Sheets("Low Priority Issues").Range("A" & nxtRow)
    Application.EnableEvents = False
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Exit Sub
End If
End Sub