Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook

{ 8 Comments }

Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions – .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start from cell B3 (first header cell), there can be a variable number of rows and columns from cell B3 onwards.

The task is to consolidate data from sheet1 of all Excel files to multiple worksheets of one Excel file.

The Question and Instructions have been clearly explained in the “Question and Instructions” worksheets of this file.

To see the solution work,

1. Save the following zipped folder on our desktop.  Unzip the folder and place Data Files folder (with all its files) on your desktop; and
2. Change the path in cell B2 of worksheet “Query and file listing” of Destination.xlsx file to the path of your folder saved in step 1 above.
3. Click on the blue button on worksheet “Query and file listing” of Destination.xlsx file.

Leave a Comment

Your email address will not be published.

*

  • Hi Asish,

    this is what i was looking for … Thanks a ton to you for your quick and crystal solution to my query.

    Regards,
    Kamal M.

  • Assume a folder named “All branch data” on the desktop. In this “All branch data” folder, there are 30 more Excel files.
    What I want is
    1) If I open automated template
    2) And enter the path in a cell
    3) If I click on button, then all sheets data should import according to the sheet names in to template from all files.
    4) All files have multiple sheets
    I have uploaded the raw files and template in below link
    https://skydrive.live.com/?cid=18775E66716D4084&id=18775E66716D4084%21108

    Thank you
    Chandru