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.
Thank you
Irving
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