Most efficient way to grab data from excel files in a folder.

I have a folder updated daily with 5 excel workbooks. I need to automatically get data from those books into a master excel sheet daily. Each daily sheet has its own formatting but the same structure except for more or less data rows day over day.

This data needs to be added to specific tabs on the master workbook, which then populates pivot tables and other sheets in that file. Right now we copy/paste/refresh all.

Question I have is what is the most efficient way to get the data I need imported and refreshed automatically everyday?

Best scenario would be pointing each tab on the master sheet to its workbook and telling it which columns have the data in another workbook (minus headers), and then adding that days data to the end of the master sheet tabs.

Daily sheet workbooks with exact structure and filenames attached.