Combine multiple sheets of multiple workbooks in one sheet

**Djibril69** · 01-28-2013, 04:03 PM

Good evening all,

I have the following problem:
One main folder "2012", subfolders "Month January", "Month February", ... , "Month December" and within each subfolder four to five week-files "Week 01", "Week 02",..."Week 52". Each "Week xx"-file has the sheet "Data" which i would like to analyze.

I want to gather all week-data in one consolidated Excel-sheet of 2012. Thus, the rows in Week01-sheet Data are copied in the consolidate sheet, the rows in Week02-Sheet Data are copied directly under it and so on...Difficulties are that only the rows starting from Row 11 must be copied into the consolidated sheet and that the number of non-blank rows differs within each Week xx-"Data" sheet.

Does anyone have an effective solution for this?
Thanks in advance!

**oeldere** · 01-28-2013, 04:06 PM

You get better help if you post an excel file, without confidentional information.

Please also add the expected result.

**Alf** · 01-30-2013, 05:18 PM

Interesting problem so I had a go at it.

In uploaded file check that range B2 to B12 in Sheet2 has the correct values for the different folders that contain all the files from January to December.

Go to Sheet1 and run macro "ProcessAllFiles" to extract data from all files.

As I'm using command "ActiveSheet.UsedRange.Offset(10).Copy" I'm assuming that all data in every "Week xx" file starts at row 1.

Alf