Methodology for combining multiple forecasts into one - ideas

[Vaslo]

Looking for some ideas:

I have a forecast file that goes out to 6 different marketing managers. Most are not excel savvy, and having all 6's data in one will be problematic in terms of size. They whine when data gets a little big and files get slow.

I'd like to give them each a seperate forecast file and allow them to make changes, play, etc. When complete, I'd like to pull all 6 fairly quickly into my master, which does some other calcs they don't care about. A long time ago I used VBA to read files in such a way, but those were simple files with the same formats underlying. Now, these guys will have different products, different numbers of products, different customers, etc.

What's a good methodology for pulling data from 6 different workbooks and combining into one nice list that can in turn be used for other calculations.

Thanks!

Vaslo

[JBeaucaire]

On my site you will find macros that do these sorts of common tasks.

===============
'SHEET1 TO MANY WORKBOOKS
Here's a macro for parsing rows of data from one sheet to many workbooks based on one column, workbooks named for the same values in that one column.

Sheet1 to Many Workbooks

My macro names the workbooks for values in the column PLUS today's date, you can take a stab at removing the date part...or leave it in, it's a good technique.

This should work if you can find one column that designates who the MANAGER is for each row, then use that as the vCOL in the macro to split out the data.

===============

'WORKBOOKS TO 1 SHEET STACKED

Here's a macro for collecting data from all files in a specific folder. Each workbook is expected to have the same COLUMN organization, though the number or rows in each workbook can be whatever it is.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

[Vaslo]

Really nice information Jerry. I am really out of practice with VBA so I have my work cut out for me.

[JBeaucaire]

I made sure the code is fully commented and highlighted in red the lines of code you probably need to tweak for your environment. You can certainly do it.

Let me know.