Consolidate multiple tables into one master table

Hi,

I am struggling to find a suitable solution for consolidating multiple tables into one master list. Any thoughts, guidance, tips or solutions would be extremely appreciated. I keep going around in circles about what's the simplest/fastest/most robust solution

Background
--> I have between 40 or 50 tables in Excel that contain data.
--> Each individual data table retains the same format each week (same number of columns, but will contain a different number of rows)
--> Many of the tables in the workbook will differ in format as some tables contain a different number of rows & columns. Every table has 2 fields in common (Unique ID & Value). These are the only fields required in the consolidated list.
--> The consolidated data table will typically contain around 2,000 metrics. Whilst that's a lot of metrics, the file size is relatively small.

Problem
--> Each week, the data in the tables are refreshed so I need to consolidate the individual tables into one master table. The only fields I need in the consolidated table are the 'Unique ID' & 'Value'.
--> The consolidated list can not contain previous weeks results. This means the consolidated list has to be cleared and rebuilt each week.
--> The current process is ridiculously manual and prone to human error. I am therefore desperately trying to find a simple, automated solution.

Question
--> What is the best approach for me to automate this solution?

Options I have thought about are :
a) Use VBA to 'copy & paste' each of the data tables into one master sheet
b) Export the tables into Access and then use a SQL query to extract the two consolidated fields
c) I have been tried to google other methods ie Power Query - but I can't find too much documents on this capability
d) MS Query
e) Power Pivot
f) Something completely different as I am barking up the wrong tree :-)


I have attached an example dummy workbook to help explain what I mean. If the above is not clear, please let me know and I will provide more details.



Fingers crossed !


Rgds,
Ceri