Run-time error '424': Object requird

[Intern2]

Hi all,

I'm a newby on VBA and have spend many hours writing the following code, but still it doesn't seem to work. Marked in red gives error '424', can anyone help me out with this?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Dim LR As Long
If LCase(Target) = "not approved" Then GoTo notapproved2
If LCase(Target) = "approved" Then GoTo approved1
Exit Sub

notapproved2:
If LCase(Target) = "not approved" Then
With Sheets("not approved")
LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=.Cells(LR, 1)
Target.EntireRow.Delete
End With
End If
Application.EnableEvents = True


approved1:
If LCase(Target) = "approved" Then
With Sheets("approved")
LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=.Cells(LR, 1)
Target.EntireRow.Delete
End With
End If
Application.EnableEvents = True

[Norie]

When you delete Target.EntireRow you delete Target, so when the code reaches here Target doesn't exist.

Please Login or Register  to view this content.

Try this.

Please Login or Register  to view this content.

PS Can you add code tags?

[Intern2]

Norie, thank you so very much! It works perfect!