Automating a manual copy paste from daily files to a consolidated file

Hi Everyone,

I currently have the data for revenue breakup by products in different excel files for each day.


Excel file 1.Contains revenue details for day 1
Excel file 2.Contains revenue details for day 2....So on for 365 daily files


I want to create one consolidated excel file that can automatically copy the data from all the daily files onto one excel sheet. I though of the below but there were some constraints that came by my way. Please suggest if there is any way to overcome these.

1.) Option 1.) An edit link - However I have 365 daily files so it would not be feasible. In addition it would be cumbersome to manually link 365 files
2.) Option 2) Macro/VBA that automatically copies from daily file and paste the data onto consolidated file. I tried it but it threw up an error as it needed the files to be open and I cant have 365 files open at same time.(Is there a code which can automatically open the required file, copy paste, then close the file, then automatically open next file, copy paste, then close ?)

Can anyone suggest a solution (A formula or a code or any other way?).

Attached some excel sheet daily and consolidated.

Many Thanks.