+ Reply to Thread
Results 1 to 6 of 6

consolidating data from multiple workbooks

  1. #1
    Registered User
    Join Date
    06-30-2010
    Location
    soton, uk
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question 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

  2. #2
    Registered User
    Join Date
    06-30-2010
    Location
    soton, uk
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: consolidating data from multiple workbooks

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

    cheers
    Annie

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: consolidating data from multiple workbooks

    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

  4. #4
    Registered User
    Join Date
    06-30-2010
    Location
    soton, uk
    MS-Off Ver
    Excel 2003
    Posts
    5

    Thumbs up Re: consolidating data from multiple workbooks

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

  5. #5
    Registered User
    Join Date
    06-30-2010
    Location
    soton, uk
    MS-Off Ver
    Excel 2003
    Posts
    5

    Red face Re: consolidating data from multiple workbooks

    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...

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: consolidating data from multiple workbooks

    Hi adams20071;
    I didn't look at Ron's code. Just looking at the line you quoted.
    Please Login or Register  to view this content.
    This line will add a new worksheet to the current workbook
    Please Login or Register  to view this content.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1