Combine 3 separate workbooks, each with 6 worksheets

[billycasper]

Hope someone can help.

For some bizarre reason, I volunteered myself for this thinking it would be easy for someone with mediocre abilities with excel.

Three colleagues each use a spreadsheet to keep a record. The spreadsheet consists of 6 worksheets. 5 are for each day of the week and the 6th produces a weekly report.

I need to be able to take each individual weekly report and add all of the data into a master weekly report whose layout is exactly the same as the individual report.

Anybody able to offer any guidance?

[mike7952]

Would be helpful if you could upload sample workbook of the exact data structure of the workbooks your trying to combine and how you want the end result to look

[billycasper]

Here is a sample copy.

There'll be 3 copies of this in the same folder. The name will always change from week to week.

The end result will be a new spreadsheet with just one worksheet that is formatted exactly the same as the weekly report. I would like this to grab all the data from the three individual workbooks, total it and fill in the relevant cells.

If it's easier, there's no issue with starting off with a workbook that just has the one worksheet and then pulling the data into that.

[mike7952]

Where will the data be on the Monday thru Friday sheets that you want to get? Column S?

[billycasper]

The data on the Monday to Friday sheets will automatically populate the Weekly Report sheet. Once that's done, I don't need to do anything with those sheets. At the end of the week I need to be able to take each of the 3 Weekly Reports from each of the 3 workbooks and add them together in a new workbook. The layout of the new workbook would be exactly the same as a Weekly Report Sheet but it'd be titled something like Total Weekly Report.

Thanks