Using VBA to pull info from various (around 5 Workbooks) into 1 big table using a Prime Ref Code

[howaysis]

I can use an ADODB.Recordset to bring data from a Named Range from a specific Workbook, however what I would like is the Final Presentation Table to bring the different data (columns) for each Prime Reference Code (so the final table might only have 40 predefined) and it will only pull in that data for each Prime Code.
Does anyone know how I can do this. I know I could bring each total Worksheet in and have a Vlookup in my Final Table but I'd rather handle at a push button to pull the data in without formulas. Appreciate any advice / known code to cover this request.

Thank you.

Howaysis

 

[SpillerBD]

IMHO, the best method currently available from Microsoft is using the Get and Transform/Power Query tools and present using PivotTable (maybe with some better management with PowerPivot.)
Like a macro, you do the hard-work on the front end and then its just data refresh. Using a Target folder to read from would mean adding additional Workbooks to be read from is just a drop into the folder.
If the end use doesn't require view of the Final Table, only load into the Data Model to improve performance.

 

[howaysis]

Thanks for the comment, unfortunately I'm using Excel 2013 and can't use Add-Ins, so I'm limited to using Excel Basis 2013 and whatever I can do with that.

 

[SpillerBD]

Guess I've missed the need for ADODB in my VBA experience.
Did you already find https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/append-method-ado
or related from Microsoft?
I usually dive into the MSDN though it seems to get missed and well publicized.