Create New Spreadsheet of data from many Excel workbooks

Solution 1:

I would use the Power Query Add-In for this. You can start a Query from an Excel table. If you spreadsheets are in separate files it has a great function to import all the files in a Windows Folder in a single step and append all their data together (assuming the file columns are consistent). If they are multiple sheets in a single file then you will need multiple Queries, which you can combine together using the Append command.

http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx?CTT=5&origin=HA103993872

There is also an Unpivot command to transform data stored in multiple columns into multiple rows - maybe that would solve your multi-Year challenge.