Consulting

Results 1 to 3 of 3

Thread: Compare Data In Multiple Sheets & Copy Duplicates

  1. #1
    VBAX Newbie
    Joined
    Feb 2019
    Posts
    4
    Location

    Compare Data In Multiple Sheets & Copy Duplicates

    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.

    Screen Shot 2563-03-11 at 11.18.41.jpg

    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

  2. #2
    VBAX Newbie
    Joined
    Feb 2019
    Posts
    4
    Location
    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?

  3. #3
    VBAX Newbie
    Joined
    Feb 2019
    Posts
    4
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •