Remove Duplicates from previous or multiple sheets.

[Benji3899]

Hi Guys, I'm fairly new to VBA and need a bit of help.

I'm trying to make a VBA macro that would remove duplicates from column 1 of previous sheets and scales with how many sheets are in the workbook.

The idea being that as number is entered into sheet 2 (column 1,) it is removed from sheet 1 (column 1)

I can find examples that will delete the duplicates from the same sheets but not others.

Any assistance will be greatly appreciated!

 

[mumps]

I'm not sure if I understood correctly but try this macro. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Enter a number in column A of any sheet and press the ENTER key.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    Dim fnd As Range
    If Sh.Index > 1 Then
        Set fnd = Sheets(ActiveSheet.Index - 1).Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Delete shift:=xlUp
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[kvsrinivasamurthy]

Worksheet event is used for Sheet2.
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PreSh As Worksheet
Dim Frng As Range
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Set PreSh = Sheets("Sheet1")
With PreSh
Line1:
Set Frng = .Range("A:A").Find(what:=Target, lookat:=xlWhole)
If Not Frng Is Nothing Then
Frng = "": GoTo Line1
Else
GoTo Line2
End If
End With
Line2:
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

This code will erase contents in Column 1 of Sheet1. It will not delete the cells.

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[Benji3899]

Thank you guys, they both work and are exactly what I needed!!!

Legends!

 

[mumps]

Glad to help.

 

[Benji3899]

Glad to help.

Ok, so I have found an issue. The script works really well but I have been informed that it needs to work slightly differently.

My understanding is it currently deletes the entry from the previous sheet.

What I need it to do is if an entry moves from any sheet the last entry is deleted.

If the number sits on sheet 1 but then get entered say sheet 3 or 4 the sheet 1 needs to get deleted and backwards too. So sheet 4 to sheet 1.

I hope that makes sense

 

[Benji3899]

Mumps, have you got any help? your first answer was spot on.

 

[Benji3899]

Glad to help.

Mumps, have you got any help? your first answer was spot on.