Hello Matt,
Try the following:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Not Intersect(Target, Range("J:J")) Is Nothing Then
If Target.Value = "No" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
Range("G" & Rows.Count).End(xlUp).Value = Range("G" & Rows.Count).End(xlUp).Offset(, 1).Value + 1
End If
End If
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value = "Remove" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
Sheet3.Columns.AutoFit
End If
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
You now have two criteria columns (J & K). Column K has the "Remove or Keep" drop downs so if you select "Remove", the relevant row of data will be transferred to Sheet3 and will be deleted from the Tracker sheet.
The code works as previous for Column J.
Following is the link to the updated test work book:-
https://www.dropbox.com/s/1jg78qr58cbrtbt/Matt%283%29.xlsm?dl=0
I hope that this helps.
Cheerio,
vcoolio.
No, the range will not always be Row 4, but it will always be Columns A to E.
Thanks
This is excellent - exactly what I was after.
Thank you very much!
Matt