Copy duplicates to new sheet

ellac

New Member
Joined
Oct 4, 2004
Messages
37
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Oct 4, 2004
Messages
37

ADVERTISEMENT

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

Well-known Member
Joined
Nov 17, 2004
Messages
708
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

New Member
Joined
Oct 4, 2004
Messages
37
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
 

Forum statistics

Threads
1,148,219
Messages
5,745,452
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top