concatenate tables

[each]

I have a dozen separate tables of data stored on different sheets within a workbook. The amount of data (number of records) changes frequently.

I would like to be able to create a third spreadsheet that concatenates all of the other sheets into a single database. (Needs to remain in Excel)

I have tried using multiple pivot ranges but cant get what I need due to the limitations of this kind of pivot.

I have tried using MS query but cannot work out if you can concatenate fields between different tables (like you can in Microsoft Access).

Of course I could just keep cuting and pasting the data but changes are too frequent and I need to overall database to update automatically.

Does anyone have any suggestions?

[steven1001]

"I have tried using MS query but cannot work out if you can concatenate fields between different tables (like you can in Microsoft Access)"

in the new spreadsheet you can use msquery and create a union of the other spreadsheets... does not have to be in a pivot table .. you can just return the data with the query.
see http://www.contextures.com/excelfiles.html#Pivot
"PT0007 - Multiple Consolidation Ranges -- Create a normal pivot table from multiple ranges, by using MS Query to join the data. One sample file contains the pivot table, the other contains the source data tables. Contains macro to update connection. PivotMultConsolPivot.zip 11 kb PivotMultConsolProv.zip 6 kb 03-Oct-05"