Yes, Shippey, it is possible.
Assuming the spreadsheet has been saved and for simplicity that each worksheet has data in exactly the same fields. (It need not, it just simplifies the explanation. This is a rough guide to get the answer. In practice, you might do things with a bit more sophisticated.)
Give each data range a defined name. Not dynamic. Say, Table1, Table2, Table3, etc.
Then menu Data, Import External Data, New Database Query. Select the Excel file, then Table1 field/s.
For this one, we won't filter. Or sort. Select the option to view & edit in Microsoft Query before hitting FINISH.
You should see the field/s from Table1 and the first screenful of records.
Now hit the SQL button (or View, SQL) and edit directly in the window to obtain,
SELECT *
FROM don't change this bit.Table1 Table1
UNION ALL
SELECT *
FROM don't change this bit.Table2 Table2
UNION ALL
SELECT *
FROM don't change this bit.Table3 Table3
Where after the first two lines (SELECT * FROM whatever) there are groups of three lines per data range. Each group being of form
UNION ALL
SELECT *
FROM don't change this bit.TableX TableX
You can copy & paste in the SQL window.
When this is done, put the data in Excel via the file menu or the "door" icon on the toolbar. And when the dialog box comes up select "Create a pivot table report..." You might get some error messages but hit cancel and then finish to see the new pivot table. You now have a pivot table with data from the multiple sources. (There are some side effects of this method because of the data query. Main one is if you save the file in a different directory, or rename it. The way we have created it leaves a hard coded address in the SQL and might be a nuisance. Again, this is not a sophisticated example and I am trying to write just enough rather than too much.) HTH, Fazza
