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