consolidating data from multiple workbooks

Hi there, I want to design a macro or code to consolidate data from multiple workbooks. I am afraid I am a complete beginner on VBA...and proper use of macro's.. So even tho I found a similar post (http://www.excelforum.com/excel-prog...worksheet.html) and had a look at the macro's I still couldnt work it out.

Can anyone signpost me to a tutorial that would help? FYI, this is for a small business my family run – I am not involved and currently the business is very small with little £ available for investing in IT systems, which is why I offered to help out. Obviously in the long run a commercial solution might be better.

What I want to do
• The data i need to consolidate are customer orders, in separate xl book’s, all saved within one folder.
• I want to consolidate data from 3 columns into a master order on one xl sheet in a new book in the same folder.
• the format of info to consolidate will be the same.
• The customer order is a form staff fill in, with quantity and product codes
• Each separate customer order could have 482 product lines from the total 2076 product lines on the catalogue
• Within an individual order, a lookup formula is used to complete the item description cell and unit price cell, checking whether the product code entered by staff matches a code in a separate xl workbook in the same folder, which is essentially the product catalogue, (the lookup goes to 65,536 product lines just in case!).
• The data in the orders to consolidate from (excl column labels which are on row A19:E19)
o ‘Qty’ A20:A482
o ‘Code’ B20:B482
o ‘Unit Price’ C20:C482
o I then in column D of the master order xls book want to multiply col A by C to calculate total line price
• However, name and number of separate customer order xl books could vary greatly, so I need to look at all excel books within a folder, excluding the actual catalogue (so maybe I should just move that file to avoid problems?)
• I thought maybe we should
o scan all workbooks for a certain code in B20:B482
o add up all the Qty values in the corresponding row in column A of the orders,
o then maybe just use a lookup function for the Unit price, to go to the catalogue in the same folder?
• Ideally I want to add a button on the master order sheet, so staff can click to run the macro or code.


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 Order book
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 to tutorials much much appreciated.

Annie

just bumping this, hope u dont mind any signposting to tutorials or guides would be really appreciated

cheers
Annie

Hello Annie,

Could something from here help you out?

http://www.rondebruin.nl/tips.htm

Look at Merge data from all workbooks in a folder (1) under Copy/Paste/Merge examples

thanks v much jeffrey, that will deft. get me started

Hi guys I want to use Ron de Bruin's very helpful VB code to collate data from multiple workbooks... all going well, but i dont want to create a new worksheet - but have a workbook with a macro assigned button that collates the data in the existing workbook. So i think the code i need to change is

Please Login or Register  to view this content.

any suggestions?

FYI - I used ron de bruin's first example from http://www.rondebruin.nl/copy3.htm just with the first cell changed to A19 and my path changed to MyPath = "C:\Documents and Settings\Suzie Blue\Orders"

any help much appreciated. i am sure it is really obvious...