Consolidate data from mltiple spreadsheets into one.

[Pugolas]

Hello Folks,
I have approximately 1000 sets of 80 excel spreadsheets. I am trying to combine each of these sets of 80 spreadsheets to produce one main spreadsheet for each of the 1000 sets.
Basically at this stage I would need to copy and paste data from each individually, onto one central 'Master' sheet.
Doing this 1000 times will take a ludicrous amount of time, so I am hoping someone has an easier way.

I have looked at merging and consolidating, but neither of those options will give me the desired effect. i.e. They want to combine all the data rather than add cumulatively.

I am hoping someone has an easy solution (and understands what I am trying to explain!).

Thank you in advance

 

[sheetspread]

Is this helpful:

http://www.mrexcel.com/forum/excel-...sheets-omits-some-data-because-usedrange.html


?

 

[Pugolas]

I wish I could say yes, but it seems to be more about merging data into a combined form spreadsheet, rather than simply having the data added on to the end. Also, the work involved in getting this to work for my purposes would be considerably more than copy and pasting from spreadsheets.

 

[sheetspread]

Do you have 1000 workbooks, each with 80 tabs, and you want the 80 tabs combined into one in each workbook?

 

[Pugolas]

No, I have 80 spreadsheet 'docs' that I want to use to create a master list. Each of these 80 spreadsheets contain a different category.
Then basically this will need to be done for each of the 1000 groups. Every spreadsheet is separate, there are no tabs involved.

I did look at consolidating or merging, but the work involved in doing this was considerably more than just c&p into a master spreadsheet.

 

[sheetspread]

Then there are 80 xls files, with a single tab each, and within each file 1000 tables?

 

[Pugolas]

Sorry, I must be explaining this badly.
There are 1000 folders. Each folder contains 80 spreadsheets. I want each folder to only contain one.

 

[sheetspread]

Would an acceptable solution loop through each folder and merge each set of 80 spreadsheets into one? If not, why?

 

[Pugolas]

If I am understanding your suggestion properly, I think that would work.

 

[sheetspread]

See if these help:

Combine the Workbooks in a Folder

Merge data from all workbooks in a folder

excel - VBA Loop Over Several Directories and Merge Data in Summary Workbook - Stack Overflow