It looks very likely that Power Query (aka Get & Transform Data in Office 2019) could fetch this data for you very easily; especially that the tab name is constant. Are these data in the source files in tables (proper Excel table as in Insert|Table)? The fact that headers are in different orders in the source files is neither here nor there, and you'll be able to bring in the data according to the column headers in your receiving file.


If you could zip up some realistic mock-ups of those four files (each with only a little data in, and of course, de-sensitised), along with a 'Master' receiving workbook and attach the zip file here I'd expect to be able to provide a solution only requiring a refresh (like you do a pivot table) and no macro/vba code, to update with new data.


As an aside, how's that data going to be used once it arrives in the Master workbook?