VBA code to check for and delete duplicate entries, then copy and paste data across sheets, with timestamp, triggered by drop down

[handoverhammer]

First and foremost, you guys rock!

Last week I learned how to copy and paste data based on drop downs. Thanks Fluff. Over the weekend, I added timestamps and message boxes.

Now, I'm wondering, can we first check for and remove any duplicate/previous entries, excluding the source, then copy and paste?

As it stands, the drop downs are in sheet1 and then depending on the selection (two, three, etc), it pushes to the corresponding sheet and creates a new entry on said sheet.

If I change the drop down on sheet1 and something has already been copied to another sheet, I want the new selection to first delete other instances of the data, excluding the original data on sheet1, then copy to the new selected sheet.

Here's what we have right now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Target.Column = 1 Then Exit Sub
If Target.Value = ("Two") Then
Dim Response As VbMsgBoxResult
Response = MsgBox("Add to Sheet2?", vbQuestion + vbYesNo)
If Response = vbNo Then Exit Sub
End If
If Target.Value = "Two" Then
With Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)
Intersect(Target.EntireRow, Range("B:F")).Copy .Offset(1)
.Offset(1, 5) = Now
Sheets("Sheet2").Select
End With
End If
If Target.CountLarge > 1 Then Exit Sub
If Not Target.Column = 1 Then Exit Sub
If Target.Value = ("Three") Then
Response = MsgBox("Add to Sheet3?", vbQuestion + vbYesNo)
If Response = vbNo Then Exit Sub
End If
If Target.Value = "Three" Then
With Sheets("Three").Range("A" & Rows.Count).End(xlUp)
Intersect(Target.EntireRow, Range("B:F")).Copy .Offset(1)
.Offset(1, 5) = Now
Sheets("Sheet3").Select
End With
End If
End Sub

Thoughts?

 

[handoverhammer]

Oops. When I anonymized the sheet and tab names, I made a reference error.

[COLOR=#333333]If Target.Value = "Three" Then
[/COLOR][COLOR=#333333]With Sheets("Three").Range("A" & Rows.Count).End(xlUp)[/COLOR]

should read

[COLOR=#333333]If Target.Value = "Three" Then
[/COLOR][COLOR=#333333]With Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)[/COLOR]

[h=3][/h]