Intermittent Object Required Error with Target.Value

[vbabasic]

Building out a couple of processes for a spreadsheet which are dependent on the selection made in the dropdown list, but running into an intermittent Object Required error.
It's weird because it was working for a minute, then nope.

1. If cell value in column AP on 'Data' sheet changes to "Remove", then copy the information to the new row on the 'Former' sheet and delete data row entirely from the 'Data' sheet.

2. If cell value in column AP on 'Data' sheet changes to "Reopen", then copy the information to the new row on the 'Former' sheet, then clear/change specific cells on the 'Data' sheet in the target row.

3. If cell value in column AP on the 'Former' sheet changes to "Return", then copy the information to the last row on the 'Data' sheet and delete data row entirely from the 'Former' sheet. (undoing 1, essentially).

Error is happening on "If Target.Value = "Reopen" Then" BUT only when you do a "Remove".
Reopening works as expected. Return also works as expected.

1&2 (in 'Data')

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AP:AP")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Former").Cells(Rows.Count, "AP").End(xlUp).Row + 1

If Target.Value = "Remove" Then
Rows(Target.Row).Copy Destination:=Sheets("Former").Rows(Lastrow)
Rows(Target.Row).Delete

End If
End If

If Target.Value = "Reopen" Then
Rows(Target.Row).Copy Destination:=Sheets("Former").Rows(Lastrow)

Cells(Target.Row, 6).Value = "VACANT"
'F
Cells(Target.Row, 11).ClearContents
Cells(Target.Row, 12).ClearContents
Cells(Target.Row, 13).ClearContents
'K-M
Cells(Target.Row, 18).ClearContents
Cells(Target.Row, 19).ClearContents
Cells(Target.Row, 20).ClearContents
Cells(Target.Row, 21).ClearContents
'R-U
Cells(Target.Row, 23).ClearContents
Cells(Target.Row, 24).ClearContents
'W-X
Cells(Target.Row, 27).ClearContents
Cells(Target.Row, 28).ClearContents
'AA-AB
Cells(Target.Row, 30).ClearContents
Cells(Target.Row, 31).ClearContents
Cells(Target.Row, 32).ClearContents
Cells(Target.Row, 33).ClearContents
Cells(Target.Row, 34).ClearContents
'AD-AH
Cells(Target.Row, 42).ClearContents
'AP

End If
End Sub

3 (in 'Former')

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AP:AP")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Data").Cells(Rows.Count, "AP").End(xlUp).Row + 1

If Target.Value = "Return" Then
Cells(Target.Row, 6).Value = "Pending"
'F
Cells(Target.Row, 42).ClearContents
'AR
Rows(Target.Row).Copy Destination:=Sheets("Data").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If

End Sub

 

[GWteB]

In your code you're deleting Target's row at some point, so from then on Target refers to a destroyed range object.
If you intend to delete a particular worksheet range in its entirety which is part of the Target argument, you must do so at the end of the event handler.
Btw, I would recommend that you use Application.EnableEvents to prevent unwanted recursion. Set this property to False at the beginning of your code and to True at the end of your code.

 

[vbabasic]

This was it! I moved the "Remove" section below the "Clear" section and it worked like a charm. I also added in the recommended code.

Nothing was working at first, so I rebooted my computer and tried again with a fresh copy of the real workbook. I assume my Excel ran out of memory or something, but then it was working exactly as expected.

Appreciate the help!

 

[GWteB]

You are welcome and thanks for letting me know.