Consolidate data from multiple closed Excel files with different number of columns

Assume a folder on the desktop named Test.  Within this folder there is a another folder named Monthly Data.  Within this folder are MS Excel files received every month.  The number of columns in each file is not the same.  Therefore in the January .xlsx file, there would be 100 columns, in the February.xlsx file, there could be 115 columns and so on.  The number of columns in every month’s file will always be greater than the number of columns in the previous month’s file i.e. If at all the number of columns across files are different, there will only be additional columns, not fewer columns.  Therefore for calendar year 2012, if all files have been received for January to May 2012, the columns in May.xlsx file will always include the columns in other four MS Excel files.  One may very well assume the following:

1. Spelling of headings across all files are the same; and
2. Headings are always in row 1 across all files (from cell A1); and
3. Worksheet tab in which information is present in all files in named “Sheet1”; and
4. In the monthly data folder, there are only those Excel files from where information needs to be consolidated; and
5. The files could be saved in any Excel format i.e. .xls, .xlsx, .xlsm; and
6. The number of rows in each file is variable

In a Summary workbook, one may want to consolidate information from sheet1 of all workbooks lying in the Monthly Data folder.  Given different number of columns in each file, even if one had the patience to copy and paste information, it will not be possible to do so because of different structures.

You may try the following steps to consolidate information:

1. Open the most recent file, say May.xlsx, and copy the headings from row 1 to this new file (paste in cell A1)
2. Press Ctrl+F3 > New.  In the Name box, type FL.  In the Refers to box, type the following:

=FILES(“C:\Users\Ashish\Desktop\Test\Monthly data\*.xls”)

3. In cell EE2, enter =IFERROR(INDEX(FL,ROW()-1),””) and copy down till blanks start appearing.  This step will list down all file names from the Monthly Data folder
4. Save the attached Excel file on your desktop, press Alt+F8 > Macro1 > Run

You may refer to my solution in the this workbook.