Creating an Excel Report from 7 separate files using VBA

This is what I have:
- 1 Excel report with consolidated data for all customers
- 6 separate Excel files with detailed information per product, also for all customers
- Refer to attached Excel template as example

For all company names in tab “company list” it should create a separate Excel report with:
- The consolidated data in a separate tab
- If there is detailed information, this information should be copied from one of the 6 separate Excel files into a separate tab in the new workbook (next to the consolidated data tab)
- If there is no detailed information for that company, it must continue to the next detailed file

I know how to create a macro for the copy/paste exercise but not how to automate this for all 7 separate files in one go. Therefore my question:
Is it possible to create 1 macro that copies all information related to company_1 out of the 7 separate files and paste it into a new file in separate tabs?

Response very much appreciated!

Template macro.xlsx

I have a question on the format and naming of the detailed information files. Do they all have the headers in row 1. Is there a naming convention for the file names that's dependable?

If the answer to the first question is yes, then you might be able to use MS-Query to import the data into the detailed information sheets in the main book. If the files always have the same name than this is all you would have to do. If the name changes but in a predictable manner (such as with a date stamp) then some simple VBA code to change the connection string should do the trick.

If VBA code is required, you can put it in an open workbook event. MS-Query can be set up to refresh data upon workbook open.

Attach a couple of company detail files with a handful of false data, and I can pull together a bit of a demo.