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
Bookmarks