Hi everyone,

I currently have four tabs which look at sales-people.

The first tab is when a salesman sells a car

Staff number Car type Amount
A123456 Renault clio £1,000.00
A224321 Vauxhall Corsa £2,000.00
A555441 Renault Megane £1,500.00

The second is where they successfully refer the purchaser to buy insurance

Staff number Car type Amount
A123456 Renault clio £200.00
A555441 Renault Megane £400.00
A123456 Ford Focus £300.00

The third is where a salesman has taken a customer for a test drive

Staff number Car type date
A123456 Ford Focus 15/10/2011
A000931 Nissan Micra 18/10/2011

The fourth is where someone receives good customer feedback

Staff number Feedback from Feedback date
A123456 Mr Smith 18/11/2011

What I would like my spreadsheet to do is look at all the above tabs and give me a list of staff numbers that appear on two or more tabs so we can see how staff are getting on. This should be put on the ‘overall’ tab.

I’ve got the following code: 

Option Explicit

Sub ListDuplicates()

    Dim Dupes() As Variant
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim LookupRng As Range
    Dim SearchRng As Range
    Dim FoundCell As Range
    Dim Cell As Range
    Dim Cnt As Long
      
    Set wksSource = Worksheets("Car sales")
    Set wksDest = Worksheets("Insurance")
    Set LookupRng = Intersect(wksSource.UsedRange, wksSource.Range("A:A"))
    Set SearchRng = wksDest.Range("A:A")
    
    For Each Cell In LookupRng
        If Not IsEmpty(Cell) Then
            Set FoundCell = SearchRng.Find(what:=Cell, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            If Not FoundCell Is Nothing Then
                Cnt = Cnt + 1
                ReDim Preserve Dupes(1 To Cnt)
                Dupes(Cnt) = Cell.Value
            End If
        End If
    Next Cell
    
    Sheets("Overall").Range("A2").Resize(UBound(Dupes)).Value = WorksheetFunction.Transpose(Dupes)
    
End Sub

Unfortunately this only looks at two tabs and I can’t seem to get it to look at all four of them.

Ideally I’d like to avoid changing the format of the sheets (need to be separate tabs) and avoid pivot tables if possible.

Can anyone help?