VBA - Remove Duplicates Across Multiple Sheets in Workbook

[Eitel13]

Also posted here:

https://stackoverflow.com/questions...orkbook?noredirect=1#comment87610856_50294190

I have multiple sheets in a particular workbook, and n each sheet there are Employee Numbers. The sheets have already been sorted in a way that Column A is always the Employee Number.

So what I need to do is loop through all the sheets and apply the RemoveDuplicates function to delete all duplicate Employee Numbers found in Column A.

Note - I am not trying to have the Employee Number appear on only one sheet; I am trying to have the Employee Number appear only once on each sheet.

This works below:

Sub deleteDuplicate()

    Dim ws As Worksheet
    Dim wkbk1 As Workbook
    Dim w As Long

    Set wkbk1 = Workbooks("3rd Party.xlsm")

    wkbk1.Activate

    With wkbk1

        For w = 1 To .Worksheets.count

            With Worksheets(w)

                .Range("A:M").RemoveDuplicates Columns:=1, Header:=xlYes

            End With

        Next w

    End With

End Sub

But when I change this line:

.Range("A:M").RemoveDuplicates Columns:=1, Header:=xlYes

to this:

.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes

I get this error:

Application-defined or object-defined error

 

[SkipVought]

Hi,

When you get that error message, hit DEBUG (with your macro still active) and determine what sheet you are on and if, in fact, you have data in the UsedRange on that sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Eitel13]

Hi Skip,

Thank you for getting back to me..

I actually had forgotten I posted this question, sorry..

I did manage to get a working solution:

https://stackoverflow.com/questions...duplicates-across-multiple-sheets-in-workbook

Public Sub DeleteDuplicates2()
    Dim wb As Workbook, ws As Worksheet

    On Error Resume Next    'Expected error: wb not found
    Set wb = ThisWorkbook   'Workbooks("workbook.xlsm")

    If Not wb Is Nothing Then
        Application.ScreenUpdating = False
        For Each ws In wb.Worksheets
            ws.UsedRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes
        Next
        Application.ScreenUpdating = True
    End If
End Sub

Thank you again