You can use Excel VBA to automatically update a pivot table when its worksheet is activated.
There are instructions on my Contextures website, for copying VBA code to your own files.
To set this up: The final way to update the pivot table is with programming.
Click the 'options' tab that appears here then there is a big icon on the middle of the ribbon named 'change data source'. To get the right range and avoid an error message...
In order to change the data source from the ribbon in excel 2007... Go to the ribbon where it says Pivot Table Tools, Options Tab. select the contents of the existing field and delete it, then switch to the new data source worksheet and highlight the data area (the dialog box will stay on top of all windows).
You don’t have an option to create an independent pivot table.
Because the pivot tables share the same cache, this can cause problems if you group the data in the pivot fields.