Create a list of unique values from multiple workbooks

[swaggerbox]

I have over 100+ workbooks in D:\MyReport\Data. I need to consolidate the data (Column A + Column B) in "Sheet1" of all workbooks into one mastersheet (Mastersheet.xlsm) located in another folder. I want the macro not just to consolidate but also validate if the data in Column A is not duplicate from the existing master workbook. If it is unique, it should copy not just the column A data but also its corresponding column B data. Sample illustrated below.

Source Workbooks (there are more)

Book1.xlsx



Name
Organization


John
Microsoft


Jeremy
Motorola


Jackson
Samsung



Book2.xlsx



Name
Organization


John
Microsoft


George
Nike


Hillary
Adidas




Destination Workbook:

Masterworkbook.xlsm



Name
Organization


John
Microsoft


Jeremy
Motorola


Jackson
Samsung


George
Nike


Hillary
Adidas



John from Book2.xlsx has been removed because it is a duplicate from Book1.xlsx

Could anyone help?

[mana]

Option Explicit


Sub test()
Dim ws As Worksheet
Dim p As String
Dim bkn As String
Dim r As Range

Set ws = ActiveSheet

p = " D:\MyReport\Data\"
bkn = Dir(p & "*.xlsx")


Do While bkn <> ""
With Workbooks.Open(p & bkn).Sheets(1)
r = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Cells(1).CurrentRegion.Copy r
.Parent.Close False
End With
bkn = Dir()
Loop

ws.Columns("A:B").RemoveDuplicates Array(1, 2)

End Sub