Aggregating Monthly Data

[willie828]

I'm trying to design an intelligent way to pull cash flow data from ~80 files into a single summary file.

The 80 files will have 12 sheets each (1 for each month of the year) from which data needs to be pulled but kept separate.

Each of the files will be formatted the same way (revenues in one column, expenses in another, descriptions in a third)

I see the final file as a pivot table with each file as a heading and the months as sub headings.

What is an elegant way of doing this?

I can think of a few ways to brute force the problem by writing all the cell references for each section of each sheet but there has to be a better way.

Ideally I should be able to add/remove from the 80 files.

Thank you for any help!

[alansidman]

Provide us with a sample file for one year, and a sample output of what you want the final spreadsheet to look like. Are all spreadsheets in the same folder. This can be accomplished with a Macro that will loop through each file and worksheet. But we need to understand what you have and what you want.

[willie828]

This is a redesign of an old system so the design isn't set in stone. I have attached what a data file would look like. Expenses on their own column, revenues on their own, ect.

My idea for the final file is something like the image I have attached with Europe/North America being replaced with the name of the group (one data file per group), the drop down being the months instead of counties.

If possible I would like to be able to expand the months even into accounts (each group will have constant account names/numbers) but this is not essential.

All data spreadsheets will be in separate sub folders inside a main folder.

[alansidman]

I don't get the correlation between the file and the image. Help us to help you by giving us examples that relate. What you are asking for is very detailed and will require several loops within some VBA code. It is important that you be specific about all this. It is not something that can be generically created. Additionally, pictures are pretty much worthless in a situation like this. We really need to have sample workbooks uploaded. Additionally, will you require all details from each month or only the totals for each category. How many line items each month? Naming convention for the sub-folders? Naming convention for files? Eighty files--does this equate to 80 years of data? Perhaps more information about the project so that we can approach this logically.