Changing Pivot Table source

[jerredjohnson]

As we move, we are changing to a new database. I have pivot tables for
40 distributors that I update data on monthly. Can you change the Data
Source of the database? This way I wont have to redo 40 pivot tables

Thanks!

 

[steven1001]

hmmm! 40 is a lot .. are they in one spreadsheet or 40?
You can change the data source by double clicking on a single value to
create a new worksheet. Use >insert>name>define and define the data on
the new sheet as (say) "test". use the pivot table wizard back button
to get to step 1 of 3. Select the Microsoft Office list (first radio
button) and set the data range to "test". Complete the Pivot table
steps.
Then do it all again and select the new external datasource. If all
Pivot tables are in one spreadsheet then visit each one and set the
datasource as radio button 3 - Another Pivot Table. Hope it helps...
and I hope someone has a better method.
regards..