You might try a Multi Consolidation Pivot Table, however, they can prove limited in terms of their flexibility.

If as implied by your profile you're using XL2010 it might be you could use Power Pivot Add-In but I confess I have little knowledge of the application and it may not prove feasible (you might need each sheet to be a stand alone file).

Again, assuming XL2010, if you're saying you have too much data to consolidate into one single sheet (ie in excess of the 1m odd rows available) then I concur that you should be using a database as the repository in the first instance (not XL).