+ Reply to Thread
Results 1 to 4 of 4

Extract data from multiple worksheets in multiple workbooks into one master summary file

  1. #1
    Registered User
    Join Date
    09-26-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Extract data from multiple worksheets in multiple workbooks into one master summary file

    Hi,

    I am looking for a way to consolidate 48 workbooks (one for each month in a four year period). Each workbook represents a month, and has between 28-31 worksheets in it depending on the month to represent each day. In each worksheet there is a price given in 15 minute intervals over a 24 hour day, or 96 entries starting in cell c6 down to c101. I am trying to find a way to consolidate all of these worksheets into one master workbook. Not only that, but I need to average the prices from 15 minute intervals into one hour intervals.

    In summary, I have 48 workbooks containing 28-31 worksheets in each with data listed in 15 minute intervals that needs to be consolidated down to 1 workbook with 4 worksheets (one for each year) listed in one hour intervals. Is there anyone who can help point me down the right path or help me out?

    Cheers!

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Extract data from multiple worksheets in multiple workbooks into one master summary fi

    What is your naming convention for the files? How are you able to distinguish between files of 2011 v/s 2012?

    Do you have a sample file that you can upload?

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    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
    09-26-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Extract data from multiple worksheets in multiple workbooks into one master summary fi

    sample.xlsx

    The file is too large (over 8 mb) so I have attached a sample. In each workbook, there are two irrelevant worksheets in the beginning, plus a worksheet for each day labeled by the number of the day (these are what I care about). There are also irrelevant worksheets called price corrections that I do not need. The only worksheets that are relevant are the ones labeled by day, for example for the first day of the month the worksheet would be titled "1". Each workbook is labeled consistently: 20xx-xx_BES.xls (year-month_BES.xls)In the sample I have attached, it includes just two days. The entire file can be downloaded here http://www.ercot.com/mktinfo/services/bal/2009/index relatively quickly.

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Extract data from multiple worksheets in multiple workbooks into one master summary fi

    Do you want data from columns B to AN to be consolidated only?

+ 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