Finding Duplicate values from multiple sheets, then creating a new sheet and with all the duplicates and what sheets are the duplicates in?

[krishssagar]

Hey guys! Hope all is well. Cant figure out on how to display the duplicate values and most importantly the code on how to display on what sheets they are present in? Duplicate value can be present in multiple sheets. Let us say the value "1" is present in sheets 1,5,7. On the new sheet I would have two columns which has Duplicates and On what sheets they are present.

So far the code I have is:

Sub acctDup()
Dim sh As Worksheet, sumsh As Worksheet, lr As Long, c As Range, cnt As Long, i As Long, rng As Range
Sheets.Add Before:=ThisWorkbook.Sheets(1)
Set sumsh = ActiveSheet
sumsh.Range("A1:A2") = "x"
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> sumsh.Name Then
            lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
            sh.Range("A2:B" & lr).Copy sumsh.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
Set rng = sumsh.Range("A3", sumsh.Cells(Rows.Count, 1).End(xlUp))
    With sumsh
        For i = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If Application.CountIf(rng, .Cells(i, 1).Value) < 3 Then
                Rows(i).Delete
            Else
                .Range("A2", .Cells(Rows.Count, 2).End(xlUp)).AutoFilter 1, .Cells(i, 1).Value
                cnt = rng.SpecialCells(xlCellTypeVisible).Count
                If cnt >= 3 Then
                    .Cells(i, 3) = cnt
                    .Range("A3", .Cells(i - 1, 2)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End If
                .AutoFilterMode = False
                i = i + 1 - cnt
                cnt = 0
            End If
        Next
    End With
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of at least two of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).