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.
Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook
{ 8 Comments }