You probably know that it is possible to use a "Multiple Consolidatio
Range" as source for a PT. I have not tried this across multipl
workbooks (AFAIK it is possible) but, for my purposes, I did not fin
that it worked very well.
Your best method is to add your data into a single table. It is quit
easy to set up macros to do the job. Incidentally, I have found th
Copy/Paste method is much faster than transferring individual rows o
data.
I do monthly financial reporting and add current month data (eg
extracted from Oracle) to the end of a table. When updating the curren
month it is, again, satisfactorily fast to delete the current mont
data completely and replace it with the whole month so far.
Finally, I find it better to not attempt any formatting or presentatio
of data until *after* the Pivot Table stage. It is very easy t
transfer data to the final report using functions like =VLOOKUP()
with, perhaps, an extra PT set up to enable this. Keeping things simpl
by breaking down into several steps makes it very easy to set the jo
up as well as cope with any subsequent structure changes that occur.
Hope this helps
