consolidate data from multiple workbooks to a single worksheet

Hi Everyone!

My vba knowlegde is at the "advanced" beginner stage and I'm currently using MS Excel 2003. I'm not sure how to go about my problem, whether vba is the way to go.......

I have a budget template with dropdown lists that gets sent to different areas to be completed then gets sent back to me. Those areas have a number of sub projects so I'll end up getting close to 100 different files. What I want to do is save all of the different budgets into a folder and then combine them all to one master spreadsheet, keeping all of the formatting. My master spreadsheet have all the account codes listed in cell A7:A250 but not all departments have access or will use all account codes, so, I thought that a code to scan the multiple workbooks for that certain code and the add up all the figures that relate to that code.

EXAMPLE
TEMPLATE 1
ACC CODE AMOUNT
1 50
2 50
3 50
4 50

TEMPLATE 2
ACC CODE AMOUNT
1 50
4 50
5 50
9 50

MASTER SPREADSHEET
1 100
2 50
3 50
4 100
5 50
9 50

So, on my master sheet, I just want the account codes with a figure to show and to have the other templates consolidated into it.

I found a vba code in rbruin's website to copy the spreadsheets but what it was doing was getting all of the different workbooks and saving them alll in one workbook with a sheet for each of the templates.

I've been researching for 2 weeks and I can't seem to understand vba language.....can anone help me or point me out to the right direction or suggest a book that I can buy for this? Is what I need to do even possible??

Thank you in advance!!!

Hi
save the attached file in the folder containing Budget templates. run the macro. I suggest you try it on a sample set of 5 files in a folder before you apply it to your complete set of files.
Ravi

Hi Ravi, thank you so much for this. I will try this out at work and let you know of the outcome!

Hi there, I need to do something fairly similar - consolidate date from multiple workbooks. It is not much more complicated, but I am afraid I am a complete beginner on VBA... So even tho I had a look at the macro's I couldnt work it out?

Can anyone signpost me? This is actually me doing a favour for a relative.

The data i need to consolidate are customer orders.
The customer order is a form staff fill in, with quantity and product code. A lookup formula is used to complete the item description cell and unit price cell. Each separate customer order (in seperate xls book, of which there could be 500) is max 500 order lines (there are 2500 products ish, but an individual customer order max. 500 product types .

Example cust order Ardvark

Qty Code ITEM DESCRIPTION UNIT PRICE LINE PRICE
5 BO1234 BO1234 broach £4.50 £22.50
100 BO5678 BO5678 bracelet £1.50 £110.50
3 AD0987 AD0987 necklace £50.00 £150.00


Example cust order Beaubangles

Qty Code ITEM DESCRIPTION UNIT PRICE LINE PRICE
100 BO1234 BO1234 broach £4.50 £450.00
10 BO5678 BO5678 bracelet £1.50 £11.50

Example Consolidated
Qty Code ITEM DESCRIPTION UNIT PRICE LINE PRICE
105 BO1234 BO1234 broach £4.50 £472.50
200 BO5678 BO5678 bracelet £1.50 £122.00
3 AD0987 AD0987 necklace £50.00 £150.00

Any help or signposting much much appreciated.

Annie