Copy duplicates to new sheet

[ellac]

Hi Everyone

I am still fighting with this problem.

I need a macro that will compare multiple sheets in the same Workbook, find duplicates in cells or rows and copy them to a new sheet in the same Workbook.

I thought this would be simple, but it has turned out to be a nightmare.

Please Help

 

[cherria]

I think you need to give more details, which is why you are not getting much response.

How many sheets will you be comparing, what sort of data are you comparing, what is the structure, is there some sort of key field in each sheet that will allow a lookup to be done and which is common. What do you mean by duplicate data, do you mean the whole row is identical or just one item in the row?

many many questions before anybody could take a stab at this any further than saying you can probably do it with a lookup function or macro.

 

[Von Pookie]

Just a note: It also helps if you don't start multiple threads on the same topic.

I am pointing your other threads to this one. Please keep to this thread.

 

[Joe Was]

This will search column A for Duplicates if found flags column B of that row with a flag: "Dup."

Sub myDupsAuto()
'Standard Module Code!
'Find All Duplicates on All Sheets then flag the find!
Dim SearchString, myRng
n = 1
Set S = Sheets.Application
myNext:
For Each S In Application.Sheets
myRng = "" & "A" & n & ""
MsgBox myRng
If myRng = S.Range("A65536") Then GoTo myBottom
With S.Range(myRng)
SearchString = .Value
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not F Is Nothing Then
firstAddress = F.Address
Do
S.Select
F.Select
ActiveCell.Offset(0, 1).Value = "Dup"
F.Value = SearchString
Set F = .FindNext(F)
Loop While Not F Is Nothing And F.Address <> firstAddress
End If
End With
n = n + 1
'GoTo myNext
myBottom:
Next S
End Sub

 

[ellac]

I do not want anything appearing in Column B, because these sheets have multiple columns of data.

Each sheet has the same type of data, can the code check all columns in each sheet and compare them and then copy the duplicates.

Thanks

 

[cherria]

ellac

you are still not helping yourself!

If you want anybody to take a real stab at this, you're going to have to explain what information you have, how a duplicate would be identified and what you then want to see on the resultant duplicates sheet.

 

[ellac]

Hi everyone

Sorry for the confusion.

This is exactly what I need, the only thing I am not sure of is how many sheets the workbook will have.

1. Look in each sheet, in the column ie. Column C - File #
2. Find duplicate records bases on File # in each sheet
3. Copy the duplicates to a sheet in the same Workbook called Duplicates.
4. Each time the macro is ran, it will search the whole Workbook except for the Duplicates sheet.

I hope this is a better explanation. Thanks for all the help