Compare sheets of two workbooks and import in a new workbook with duplicates ignored

[DreyFox]

Hi there,

I have a weird request. I have a Destination File named Scheduler with the following columns A and B:

Then I have a Source File called MOSTATUS, with the following columns A and B:

Both MOSTATUS and Scheduler are different workbooks.

What I would like to achieve is, when the user hits the "Import data" button, the VBA script would compare Columns A and Columns B of the Destination and the Source File, delete the duplicate rows in the Source File, delete all the data in the Destination File, and import the remaining data in the Source File into the Destination File.

I found these links, but I'm not sure how any of them could be of use to me:

Compare Two Worksheets with VBA — Excel Dashboards VBA

Compare Two Worksheets with vba, using the scripting dictionary

www.thesmallman.com

Copy named rows based on new sheet based on text comparison of two cells

Good day everyone! I hope everyone is doing well. So here's what I'm trying to do...I need a VBS that will loop through a workbook of multiple worksheets and look at columns C and D for differences as long as the text does not match "Default settings" or "Configurations settings". If there are...

www.mrexcel.com

Any help would be greatly appreciated. Thank you for your assistance and time.

 

[DreyFox]

To clarify for example MO00003831-AL in the Destination File should be compared to possible matches of MO00003831-AL in the Source File. If found in the Source File for the same match, that entire row should be deleted.

 

[DreyFox]

I also found this link in StackOverflow which could be helpful: VBA Macro to compare all cells of two Excel files

 

[DreyFox]

To help anyone who may need it in the future, this is what I ended up doing:

Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet, sh2 As Worksheet, dic As Object
  Dim r1 As Range, r2 As Range, a As Variant, b As Variant
  Dim i As Long, lr1 As Long, lr2 As Long
  Dim wb As Workbook
  Dim fPath
  
  fPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened")
  Set wb = Workbooks.Open(fPath)
  
  Set sh1 = Workbooks("p1.xlsm").Worksheets("Sheet1")
  Set sh2 = wb.Worksheets("Sheet1")
  Set dic = CreateObject("Scripting.Dictionary")
  
  dic.CompareMode = vbTextCompare
  lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
  a = sh1.Range("A1:B" & lr1).Value2
  b = sh2.Range("A1:B" & lr2).Value2
  Set r1 = sh1.Range("A" & lr1 + 1)
  Set r2 = sh2.Range("A" & lr2 + 1)
  
  For i = 1 To UBound(a, 1)
    dic(a(i, 1) & a(i, 2)) = i
  Next
  For i = 1 To UBound(b, 1)
    If dic.exists(b(i, 1) & b(i, 2)) Then
      Set r2 = Union(r2, sh2.Range("A" & i))
    End If
  Next
  r2.EntireRow.Delete
  
  
End Sub