USD $20.00 Copy multiple workbooks into 1 workbook and then summarize data

  • Hello,

    I am using Excel 2003 and Windows XP Professional version 2002

    I am not proficient at all in coding but do ok with formulas/functions.

    I have 1 folder called LTR TEST.

    In this folder I have:

    1. I have about 100 client workbooks with 3 sheets each. Only 1 sheet has data, the other 2 are blank. All are named in the format "LTR####". #### = the client number. If the client number is 4 then the file is named "LTR0004". If the client number is 59 the the file is named "LTR0059".
    The sheet is basically a template with information for the clients to fill in.

    2. One other workbook I have is named: ltr summary. This workbook currently has 3 sheets.
    a. summary sheet
    b. address data base sheet
    c. 1 client workbook (single sheet)

    3. All workbooks are stored in the same folder - ltr test

    Here is what I would like to accomplish:

    1. I would like to incorporate each of the client workbooks (single sheet) to the ltr summary workbook.
    2. I would then like to take the data from the client sheets and summarize them across columns on the summary sheet. [Line 2 shows the cells to retrieve from the client sheets, and Line 5 partially shows what the actual data retrieved would look like]
    3. I do not have any named ranges in the client worksheets.

    I have attached 1 additional client workbooks and the Summary workbook.

    I am just starting this job and need help to establish myself. I intend to study excel and take some classes but in the meantime I have not been able to find enough info to figure out how to do this or even if it is possible. I can only offer $20 at this time. Please and thank you in advance for any help.

  • Re: Copy multiple workbooks into 1 workbook and then summarize data


    Open your summary workbook. Hit Alt + F11 > goto Insert > Module and insert.

    paste this code there. Hit Alt+Q(to close VBE window)

    Go to Tools > Macro > Macros > Select ConsolidateData and click on Run.

  • Re: Copy multiple workbooks into 1 workbook and then summarize data

    omigosh - you are a lifesaver Kris!

    I am so excited. It works with just a few exceptions and questions as follows:

    1. It doesn't do the vlookup for the addresses, city, state, zip.
    2. Currently that data is being summarized starting on line 7. Can that be changed to Line 2?
    3. Also, I could not enter the actual data for for spreadsheet, and therefore, I also changed the column names. Can I easily change the column names without affecting the code?
    4. Final question - Can I add columns (for instance - if we need additional info from the client, we will have to add additional lines to the client workbooks), and if so, will I have to change the code?

    I am so appreciative of your help!

  • Re: Copy multiple workbooks into 1 workbook and then summarize data


    Answers to the questions

    1. It actually evaluates the vlookup formula. So you won't see the formulas in the cells. See my comments in code
    2. Done. See my amended code
    3. Yes, you could do that.
    4. See my comments in the code.