• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to compare multiple columns between two worksheets and copy differences

Irving

New Member
Hello,
I have a macro that will compare rows based on column "a" between two workbooks and copy any rows that are not found in the first workbook to the last row of the active worksheet.
I however now need to make it look at two columns (A) and (B), again comparing against a second workbook and copying any rows not found in the first workbook to the last row of the active worksheet.
I have wracked my head on this for a while and just cannot seem to get my macro to look at more than just one column making it impossible to do the two column comparison I need.
Below is the macro I currently use. Any help in getting this to work would be appreciated.
Code:
Sub import_Settlements()

    'Open export file
On Error Resume Next
Workbooks.Open ("G:\Netshare\Warranty\PRC LV\Settlements.xlsx")

    'Prep dl file for import. Delete first two rows and shift remaining up
Workbooks("Settlements.xlsx").Sheets("Sheet1").Activate
Worksheets("Sheet1").Rows("1:2").Delete Shift:=xlShiftUp

    'Return to master workbook
Workbooks("WARRANTY SETTLEMENT RECOVERY.xlsm").Sheets("Recovery_All").Activate
Sheets("Recovery_All").Select

        'Only run when the active file is the master list and the active sheet is the master list
        'exported file must be open already
       
        Dim exported_file As String
        exported_file = "Settlements.xlsx"
        header_exists = True 'if exported file doesn't have a header, set this to false!
        starting_row = 1
        If header_exists Then starting_row = 2

        Dim first_blank_row As Long
        first_blank_row = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
       
        Dim y As Long
        y = starting_row
        Dim found As Range
        Claim_number = Workbooks(exported_file).ActiveSheet.Range("a" & y).Value
        Version = Workbooks(exported_file).ActiveSheet.Range("b" & y).Value
        Do While Not Claim_number = ""
            'look for Claim number in master list
            Set found = Columns("a:a").Find(what:=Claim_number, LookIn:=xlValues, lookat:=xlWhole)
           
            If found Is Nothing Then
                'add new claims to end of master list
                write_line_from_export exported_file, y, first_blank_row
                first_blank_row = first_blank_row + 1
            Else

            End If
            y = y + 1
            'Claim_number = Workbooks(exported_file).ActiveSheet.Range("a" & y).Value
            'Version = Workbooks(exported_file).ActiveSheet.Range("b" & y).Value
       
Loop

Call DynamicRangeTables

    End Sub

    Sub write_line_from_export(src_filename As String, src_y As Long, dest_y As Long)
        For Z = 1 To 13
            Cells(dest_y, Z).Value = Workbooks(src_filename).ActiveSheet.Cells(src_y, Z).Value
        Next Z
    End Sub

Private Sub CommandButton1_Click()
import_Settlements
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Thank you
Irving
 
Back
Top