Excel VBA: Update rec if it exists, add record if it doesn't

[RLCornish]

I have 2 wkshts (diff wkbk's), "CurrentTracking" and "MasterTracking". "MasterTracking" is a wksht that will continually be updated and added to for the duration of a project (months); updates and new records to come from "CurrentTracking". "CurrentTracking" is a download from a freight companies website (only goes back about 45 days) with the status of a tracking # current as of the day it's downloaded.

I need a macro that will update the current status of a record on the "MasterTracking" based on what's on the "CurrentTracking". However, if the record doesn't exist on "MasterTracking", a new record should be added to "MasterTracking". The update process will be run 3+ times a week.

The unique field in both wkshts is the tracking #. The number of records in the "CurrentTracking" varies from download to download, and as I mentioned the "MasterTracking" will be added onto throughout the project.

Thoughts on how to do this easily?

 

[Stock2014]

I would loop through the tracking numbers on the CurrentTracking sheet and check against the tracking numbers in the MasterTracking sheet

Dim LR_Master, LR_Current As Integer ' Variables to hold the number of rows in each file
Dim CT, MT As Workbook
Set CT = Workbooks.Open("insert MasterTracking workbook path here")
Set MT = Workbooks.Open("insert CurrentTracking workbook path here")
Dim IsMatch As Boolean
LR_Master = MT.Sheets("MasterTracking").Range("A:A").Find("*", SearchDirection = xlPrevious).Row
LR_Current = CT.Sheets("CurrentTracking").Range("A:A").Find("*", SearchDirection = xlPrevious).Row

For i = 1 To LR_Current ' if there is a header row, change the 1 to 2
IsMatch = False
For j = 1 To LR_Master ' same deal here
If CT.Sheets("CurrentTracking").Cells(i, 1) = MT.Sheets("MasterTracking").Cells(j, 1) Then
'Put whatever update function you want here. Use the line below if you just want to copy and paste the whole row.
CT.Sheets("CurrentTracking").Rows(i).Copy
MT.Sheets("MasterTracking").Cells(j, 1).PasteSpecial xlPasteAll
IsMatch = True
End If
Next j
If IsMatch = False Then
CT.Sheets("CurrentTracking").Rows(i).Copy
MT.Sheets("MasterTracking").Cells(LR_Master + 1, 1).PasteSpecial xlPasteAll
LR_Master = LR_Master + 1
End If
Next i