For Excel 2010 or 2013, I would use the Power Query Add-In for this. From Excel 2016, Power Query is built in to the Data ribbon under "Get and Transform" section.
Power Query can start from an folder containing Excel files. It returns this as a table listing the files, which you can Filter as required. The key technique at that point is to Add a Column with the formula:
Excel.Workbook ( [Content] )
You can then expand that column to get a list of the Tables and Named Ranges in each file. Keep expanding and you will get to the row data in each table. Providing the files are consistent, you should be able to filter down to just the data you want. The result can be written out a new Excel Table.
By default this will do a full refresh each time you tell it to Refresh your Query - this is usually the most reliable method. If you need something more complicated, you can read in the "Master" data and match it using a Merge, to eliminate rows already loaded.