+ Reply to Thread
Results 1 to 11 of 11

Consolidating data from multiple workbooks into Master Workbook

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    13

    Exclamation Consolidating data from multiple workbooks into Master Workbook

    I have two Excel workbooks with multiple tabs in each (the template layout in each tab is identical).

    First, I would like to extract specific data starting with tabs AFTER the tab "MonthlySummary". Specific data wanted: Customer, Engineering Total, Equipment, Rate, Days, Total Revenue ($), and the Date. Then, arrange the extracted data into a table with headers from which, later, if you wanted to, could create pivot table/chart. This kind of table would be created in EACH workbook.

    Then, I would like to create a Master Workbook where I have two buttons with macros attached. One button would say "Load Data". This macro would enable you to browse for the file with the two workbooks, select that file and pull out the table created in the first step for each workbook. The two tables pulled out would show up as tabs in the Master Workbook.
    The second button would say "Consolidate Data". This macro would enable you to consolidate the two tables into one MasterTable from which you could also create a pivot table.

    Attached are two examples of the individual workbooks from which I want to extract data in order to consolidate it.

    test_workbook1.xlstes_workbook2.xls

    If anyone could help with ANY of these steps, it would help me a great lot! Thank you in advance.
    Last edited by zloywolf; 09-06-2012 at 02:23 PM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating data from multiple workbooks into Master Workbook

    So if i understand you correctly, you need the following - ?

    1. Consolidate certain data from each worksheet of workbook1.
    2. Consolidate certain data from each worksheet of workbook2.
    3. Consolidate workbook 1 & 2.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    07-24-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Consolidating data from multiple workbooks into Master Workbook

    Hello arlu1201!

    Yes, that is correct.

    Thank you for taking interest.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating data from multiple workbooks into Master Workbook

    Could you please specify the exact cell references for these fields - Customer, Engineering Total, Equipment, Rate, Days, Total Revenue ($), and the Date that you need consolidated?

  5. #5
    Registered User
    Join Date
    07-24-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Consolidating data from multiple workbooks into Master Workbook

    Hello! It's a bit confusing because some cells are merged so you would have to unmerge them first.
    Once you do that, the cell references are:

    For Customer: S42
    For Contract No: S45
    For Equipment: B16 - B25 (but the amount of lines may vary)
    For Total Revenue: AK 16 - AK 25 (but the amount of lines may vary from one workbook to the next, along with the equpment lines)
    For Rate: D16 - D25 (amount of lines may vary but goes along with the equipment)
    For Days:AJ 16 - AJ25 (amount of lines may vary but goes along with the equipment
    For Date: S49
    For Engineering Total: Please ignore this one, it's no longer needed.

    Thanks again for taking a keen interest!

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating data from multiple workbooks into Master Workbook

    For steps 1 & 2 in my post 2, would you have more than 2 workbooks the consolidation needs to happen on?

    If yes, are these files stored in a folder? Or do you want the user to be prompted to open each file?

    Merged cells will not affect the code.

  7. #7
    Registered User
    Join Date
    07-24-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Consolidating data from multiple workbooks into Master Workbook

    Yes, I would definitely have more than 2 workbooks. It could be up to 72 workbooks of the same format and size.
    I thought it'd be best to have them stored in a folder on a DEsktop and then have the user simply load the data from the Master Workbook by browsing for the folder.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating data from multiple workbooks into Master Workbook

    For Equipment: B16 - B25 (but the amount of lines may vary)
    For Total Revenue: AK 16 - AK 25 (but the amount of lines may vary from one workbook to the next, along with the equpment lines)
    For Rate: D16 - D25 (amount of lines may vary but goes along with the equipment)
    For Days:AJ 16 - AJ25 (amount of lines may vary but goes along with the equipment
    So there will be blank cells in columns A and B wherever there is more than 1 row of data like the above. Do you want the same data from A & B to be copied down for those rows?

  9. #9
    Registered User
    Join Date
    07-24-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Consolidating data from multiple workbooks into Master Workbook

    Arlette,
    I'm not sure what you mean. In the original, individual workbook, column A is empty and does NOT need to be copied. Let me know if I answered your question.

    Thank you.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating data from multiple workbooks into Master Workbook

    For example you have this - For Equipment: B16 - B25 (but the amount of lines may vary)

    So considering that the data will be from B16 to B20, when its copied to the master file, it will occupy 5 rows, but the data for Customer & Contract No which will be populated in A & B of the master file will have entry only in the 1st row. The remaining 4 rows will be blank. Do you want the same data from the 1st row to show for the remaining 4 rows?

  11. #11
    Registered User
    Join Date
    07-24-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Consolidating data from multiple workbooks into Master Workbook

    Okay, I understand. Yes, I would like the Customer, Contract No, Date data to show for all Equipment, so no blank lines. This is so it would be possible to create a pivot table from the Master table after consolidating all the data.

+ 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