VBA to look for duplicate item in another worksheet upon data entry

[DUMMY1]

Hello,

1. I have two worksheets in a workbook both containing same item (PART#).
2. Sheet 1 - Master List
3. Sheet 2 - Data Input
4. After data input (PART#) in Sheet 2, VBA will search for identical PART# in Sheet 1.
5. VBA will prompt an input message "Duplicate PART# found! Do you want to delete (Y/N)?"
6. If yes, VBA will delete entire row having the identical PART# in Sheet 1.
7. In the same token, the row containing the new PART# in Sheet 2 will also be removed.

I have been working on this for two weeks (with or without VBA) but nothing has worked. Could someone please help?

Thanks!.

DUMMY 1

 

[JoeMo]

What column holds the part # in sheet1, and in sheet2? Starting in what rows? With or without a header?

Do you really want both 6. and 7. if a duplicate part # is entered in sheet2 - so no record of said part# remains in either sheet?

 

[Beyond_avarice]

On the "Sheet 1 - Master List": are all part numbers in a single column?(Which one?) Or spread out?
On the "Sheet 2 - Data Input": are all part numbers in a single column?(Which one?) Or spread out?

To emphasize Joe's second question. If you identify a match and both part numbers are removed from both sheets, If you keep entering identical part# on Sheet 2; No further deletion will happen as there is no longer a reference on Sheet1. Are you sure this is what you want?

 

[DUMMY1]

Sample worksheets as follows. Both worksheets have headers.

Yes, 6 & 7 are to be executed. Upon data entry in SHEET 2, sub() will detect and notify that duplicate PART# is found in SHEET 1. After execution, no row of that PART# shall remain in either worksheet. For example, entire row containing PART# 88853 shall not appear in either worksheet.

SHEET 1
[TABLE="width: 391"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]PART#[/TD]
[TD]CFS[/TD]
[TD]DAYS LAPSED[/TD]
[/TR]
[TR]
[TD]1/1/2017[/TD]
[TD]49010[/TD]
[TD]DSD[/TD]
[TD]234[/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD]90320[/TD]
[TD]UPS[/TD]
[TD]203[/TD]
[/TR]
[TR]
[TD]8/21/2017[/TD]
[TD]88853[/TD]
[TD]NOVA[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2
[TABLE="width: 391"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]PART#[/TD]
[TD]CFS[/TD]
[TD]HANDLEER ID#[/TD]
[/TR]
[TR]
[TD]8/23/2017[/TD]
[TD]88853[/TD]
[TD]NOVA[/TD]
[TD]LA235608[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

This is code for Sheet2. It will fire automatically whenever a change is made to column B of that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, n As Variant, Ans As Long
If Not Intersect(Columns("B"), Target) Is Nothing Then
    For Each c In Intersect(Columns("B"), Target)
        If Not IsEmpty(c) Then
            n = Application.Match(c.Value, Sheets("Sheet1").Columns("B"), 0)
            If Not IsError(n) Then
                Ans = MsgBox("Duplicate PART# found! Do you want to delete (Y/N)?", vbYesNo)
                If Ans = vbNo Then Exit Sub
                Application.EnableEvents = False
                c.EntireRow.Delete
                Sheets("Sheet1").Cells(n, 2).EntireRow.Delete
                Application.EnableEvents = True
            End If
        End If
    Next c
End If
End Sub

 

[DUMMY1]

It does not work...

Run-time error '1004':
Delete method of Range class failed

 

[DUMMY1]

Yes, that's correct.

Joe posted me VBA codes but somehow there was a run-time error.

Run-time error '1004':
Delete method of Range class failed

 

[JoeMo]

Which line is highlighted when you get the error? Did you install the code in Sheet2 or as a standard module?

 

[DUMMY1]

Hi Joe,

The run-time error was caused by some protected columns in SHEET 2. I added on ActiveSheet.Unprotect Password:= and the codes are working fine now.

Thanks a lot!

 

[DUMMY1]

Hello Joe,

1. Run-time error was caused by protected columns in SHEET 2.
2. I added ActiveSheet.Unprotect Password:= in the sub() and the codes are working fine now.

Thanks a lot!