Remove Duplicates from previous or multiple sheets.

Benji3899

New Member
Joined
Oct 14, 2024
Messages
5
Office Version
  1. 365
  2. 2016
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
VBA Code:
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
 
Upvote 0
Worksheet event is used for Sheet2.
Code:
VBA 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
 
Upvote 0
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 😃
 
Upvote 0

Forum statistics

Threads
1,224,812
Messages
6,181,084
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top