Multi-Sheet Lookup & Comparison

' Multi-Sheet Lookup & Comparison
' Compare and match data across multiple worksheets with conditions

Sub MultiSheetComparison()
    ' Main procedure to compare data across sheets
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("WS1")
    Set ws2 = ThisWorkbook.Sheets("WS2")
    Set ws3 = ThisWorkbook.Sheets("WS3")
    Set ws4 = ThisWorkbook.Sheets("WS4")

    ' Clear previous results in WS4
    ws4.Range("A2:K" & ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row).ClearContents

    Dim lastRow1 As Long
    lastRow1 = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row

    Dim i As Long
    Dim outputRow As Long
    outputRow = 2

    ' Loop through each row in WS1
    For i = 2 To lastRow1
        Dim valueWS1B As Variant
        valueWS1B = ws1.Cells(i, 2).Value ' Column B

        ' Find matching rows in WS2 (Column A matches WS1 Column B)
        Dim matchRows As Collection
        Set matchRows = FindMatches(ws2, valueWS1B, 1) ' Search WS2 Column A

        If matchRows.Count > 0 Then
            ' For each match in WS2, compare with WS3
            Dim match As Variant
            For Each match In matchRows
                Dim matchRow2 As Long
                matchRow2 = CLng(match)

                Dim valueWS2B As Variant
                valueWS2B = ws2.Cells(matchRow2, 2).Value ' WS2 Column B

                ' Find matches in WS3 Column G
                Dim matchRows3 As Collection
                Set matchRows3 = FindMatches(ws3, valueWS2B, 7) ' Search WS3 Column G

' ... 115 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io