Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


VBA - Remove Duplicates Across Multiple Sheets in Workbook

VBA - Remove Duplicates Across Multiple Sheets in Workbook

VBA - Remove Duplicates Across Multiple Sheets in Workbook

Also posted here:


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:

CODE --> vba

Sub deleteDuplicate()

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

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


    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:

CODE --> vba

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

to this:

CODE --> vba

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

I get this error:


Application-defined or object-defined error

RE: VBA - Remove Duplicates Across Multiple Sheets in Workbook


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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA - Remove Duplicates Across Multiple Sheets in Workbook

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:


CODE --> vba

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
        Application.ScreenUpdating = True
    End If
End Sub 

Thank you again :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close