Consolidate specific data, based on criteria, from multiple sheets into single sheet

Hello,

I'm trying to automate a process in Excel but I don't know the best approach to do this.

Use case: I need to create a summary report on a weekly basis. The information for this report is stored in multiple excel files. Those spreadsheets are being updated every week. The spreadsheets contain the same structure and design with multiple tabs. The only thing that changes from week to week is the information. So basically I want to extract only certain information which is deemed important for the summary report. The consolidated data for the summary report need to be saved in a separated file 'EDF.xlsx' AND within one worksheet.

Example
Week 1 >
Excel file 'ABC.xlsx': data range to be consolidated ( Worksheet1 A1:A4, B1:B6; Worksheet2 A1:A6, B1:B3, ....)
Excel file 'XYZ.xlsx': data range to be consolidated ( Worksheet1 A1: A3, B1:B4; Worksheet2 A1:A8, B1:B5, ....)
:
:
Week 2 >
Excel file 'ABC.xlsx': data range to be consolidated ( Worksheet1 A1:A5, B1:B4; Worksheet2 A1:A9, B1:B6, ....)
Excel file 'XYZ.xlsx': data range to be consolidated ( Worksheet1 A1: A6, B1:B8; Worksheet2 A1:A4, B1:B7, ....)
:
:

Summary report
Excel file 'EDF v1.xlsx': consolidated information from Week 1
Excel file 'EDF v2.xlsx': consolidated information from Week 2


Any idea how to do that? If it requires programming I would appreciate it if somebody can help me with that.

Thanks

1. Will the source sheets be open or closed?

2. How can they have the same structure & design if the data are in A1:A4 one week and A1:A5 the next week?

Please upload SMALL, but representative files for two weeks, and the summary sheet, complete with expected results.

Also, why are you restricting yourself by using the (VERY) out of date .xls format, instead of .xlsx... wheich came along with Excel 2007.

Originally Posted by Glenn Kennedy

1. Will the source sheets be open or closed?

preferably closed but can also be open if required.

And the sample sheets???

1. Is this an Excel question or a Google sheets question? If it is Google sheets, I shall move it to the GS sub-forum and bow out.

2. If it is an Excel Q... up load Excel files HERE not on an external site (see yellow banner for instructions) laid out as you get them and/or want to see them.

3. I can see no logical pattern here. The entries seem to pop into place entirely randomly. Explain.... in full.