I’ve been trying to write this VBA code to automate the process of finding differences between 2 original source sheets and then copying the entire row of the differences into a new sheet.

the process should be like this:

• The code should look into Column A for both source sheets, finding only the right value to start the comparison, for example: InStr(cell1.Value, "1") > 0
• after that, the code should look into column B for the actual differences. if the code finds a difference, then it would then copy the entire row where that different value is in. for example: the differences is in B2 in sheet A and B3 in sheet B, then it would copy the entire row 2 from sheet A and row 3 from sheet B to the new sheet
• When the code paste to a new sheet, it should paste the rows from sheet A at from the first column, and rows from sheet B to 3 columns after the end columns of sheet A. for example: Since both source sheet has the same amount of columns, so when pasting sheet A to the new sheet, it should be from Column A to AJ, then pasting rows from sheet B should paste into the new sheet from Column AN to BW, leaving AJ, AK and AL free

Here is the code that I’ve tried to make so far. Do note that I have not add all feature i wanted because I’ve been getting errors from the result.

Sub GetLevel1Difference()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim col1 As Range, col2 As Range
    Dim cell1 As Range, cell2 As Range
    Dim wsResult As Worksheet
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim i As Long
    Dim j As Long
    Dim resultRow As Long
    Dim maxCols As Long
    Dim rng1 As Range
    Dim rng2 As Range

    ' Set the worksheets
    Set ws1 = ThisWorkbook.Sheets("Sheet 1") ' Change "Sheet1" to your first sheet name
    Set ws2 = ThisWorkbook.Sheets("Sheet 2") ' Change "Sheet2" to your second sheet name
    
    Set col1 = ws1.Range("A:A")
    Set col2 = ws2.Range("A:A")
    
    
    ' Create a new worksheet for results
    Set wsResult = ThisWorkbook.Sheets("ComparisonResult") 

    ' Get the last used row in columns of both sheets
    lastRow1 = ws1.Cells(ws1.Rows.Count, col1.Column).End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, col2.Column).End(xlUp).Row
    
    ' Get the maximum columns used in ws1
    maxCols = ws1.UsedRange.Columns.Count + 3
    
    ' Initialize the result row counter
    resultRow = 2
    
    ' Loop through each row in the first sheet
    For i = 2 To lastRow1
        ' Find matching row in the second sheet
        For j = 2 To lastRow2
        
            Set cell1 = col1.Cells(i, 1)
            Set cell2 = col2.Cells(j, 1)
            
            ' Compare values in the specified column
            If cell1.Value = cell2.Value And InStr(cell1.Value, "1") > 0 And InStr(cell2.Value, "1") > 0 Then
            
                ' Define the range for the row to copy from ws1
                Set rng1 = ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, ws1.UsedRange.Columns.Count))
                
                ' Define the range for the row to copy from ws2
                Set rng2 = ws2.Range(ws2.Cells(j, 1), ws2.Cells(j, ws2.UsedRange.Columns.Count))
                
                 ' Copy the entire row from the first sheet to the result sheet
                rng1.Copy
                wsResult.Cells(resultRow, 1).PasteSpecial Paste:=xlPasteValues

                ' Copy the entire row from the second sheet to the result sheet, offset by maxCols
                rng2.Copy
                wsResult.Cells(resultRow, maxCols).PasteSpecial Paste:=xlPasteValues
                
                ' Increment the result row counter
                resultRow = resultRow + 1
                Exit For
            End If
        Next j
    Next i
    
    ' Clear the clipboard
    Application.CutCopyMode = False
    
    MsgBox "Comparison and copy completed!", vbInformation

End Sub

The code does what it should do for the 1st sheet, finding the right value for Column A then pasting it to the new sheet at the right location.

However, For the second sheet, it copy the same values from the first result it found and then pasting it all repeatedly until it matched the amount of result from the first sheet. It should be noted that the 2 sheets have a different amount of rows.

Here is the result from my own excel page. some columns has been hidden for readability, but the 2nd part constantly repeat itself: