Finding the differences then copy and paste code to a new sheet

[Nano]

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.

Can anyone help with this?

 

[myall_blues]

I think we need more information to be able to help with this.
It's not clear to me what you mean by:

• 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

In a typical pair of Excel columns generally every value in one column will differ from the value in every row of the other column except one. Do you mean that it should take the value in B2 and compare it against every other row in column B from the other sheet looking for differences, then repeat taking value in B3 and compare to every other and so on?

Also it's very hard to reproduce/fix/test issues without data. Can you paste a data sample using XL2BB or uploading a sample workbook to Onedrive or similar and sharing the link?