Hello i am trying to create a sheet that will auto copy an entire row to the achieve sheet and then delete the data from the copied row.
Its essentially an order form and once the object has arrived then it needs to move to the archived sheet for records, and removed from the "outstanding sheet"
I have managed to sort the copy part with the following VBA but cannot seem to figure the delete row section, once its been copied.
current VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And UCase(Target) = "ARRIVED" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Application.EnableEvents = True
End Sub
any help would be greatly appreciated.
many thanks
Its essentially an order form and once the object has arrived then it needs to move to the archived sheet for records, and removed from the "outstanding sheet"
I have managed to sort the copy part with the following VBA but cannot seem to figure the delete row section, once its been copied.
current VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And UCase(Target) = "ARRIVED" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("19P1 ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Application.EnableEvents = True
End Sub
any help would be greatly appreciated.
many thanks