Compare Data In Multiple Sheets & Copy Duplicates

[honkin]

Hi
I have been struggling to work out a way to do what I need and I hoped someone here might have a solution.

I have a workbook with 10 sheets in it. I wish to compare 3 of the sheets in this workbook daily using only the data in columns A, B & H...these are Date, Time and Horse. If the data in these 3 columns matches across any of the 3 workbooks, the entire duplicate row is pasted into a sheet named Confirmed Lays. Naturally as this will be run daily, it would be preferable to not have any data which had been copied on previous days also copied across, though I guess a remove duplicates argument could handle that. it would also be preferable not to have the header row copied across.

26144

The sheet names for the comparison are Safe Bets Lay, FA Lays 1 and FA Lays 2. There is one issue I can see and that is in the sheet FA Lays 2, there is an additional column added, P (Race Value) which is not in the other 2; other than that, they are identical. To keep the results in Confirmed Lays looking the same, it would be beast to exclude this column if at all possible. Possibly a Case statement could be used to exclude that column.

Thanks so much in advance

[honkin]

I have come up with some code on my own, but it compares all of the sheets in the workbook and I only want 3. I tried excluding the ones I don't want with this


Sub SetUp()


For Each ws _
In ActiveWorkbook.Sheets


Select Case ws.Name
Case Is = "Safe Bets", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
'Do Nothing
Case Else
ws.Tab.Color = xlNone


If ws.FilterMode = True Then
ws.ShowAllData
End If


If ws.AutoFilterMode = True Then
ws.AutoFilterMode = False
End If


If ws.Name = "Criteria" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
End Select
Next ws


Worksheets.Add.Name = "Criteria"
Worksheets("Confirmed Lays").Range("1:1").Copy Worksheets("Criteria").Range("1:1")


End Sub


yet it still compares all sheets

Any thoughts?

[honkin]

This is the next part of the code


Sub LoopWSs()


For Each CritWS In ThisWorkbook.Worksheets


CritWSLastRow = CritWS.Cells(Rows.Count, 1).End(xlUp).Row

For Each currentWS In ThisWorkbook.Worksheets
If CritWS.Name = currentWS.Name Then
GoTo Skip
End If

If currentWS.Name = "Criteria" Then
GoTo Skip
End If
If currentWS.Name = "Confirmed Lays" Then
GoTo Skip
End If

currentWSLastRow = currentWS.Cells(Rows.Count, 1).End(xlUp).Row
Call FilterWSs
currentWS.Tab.Color = vbWhite
Skip:
Next currentWS
CritWS.Tab.Color = vbWhite
Next CritWS


End Sub

Am I maybe running the Select Case argument on the wrong section of my code? It is currently in the SetUp() macro