On copying pivot table and changing datasource some calculated formulas disappear

[Stepjack]

I have 10 datatables on 10 tabs in a workbook, each tab named 1 to 10. Each pulls in the same data types by column, the data in the rows being diffferent. Each datatable will pull in various number of rows of data. As such I created named ranges (data1 for tab 1 data; etc.) and use an offset formula to ensure all data is grabbed.

There are many calculations that I have created in the pivot table to reduce file size when pulling data (e.g. Avg Price = Dollars / Units).

I have just made some updates to my formulas in the first pivot table on tab 1A associated to datatable data1 on tab 1. I now want to copy the pivot table with the formulas accross the other tabs 2A to 10A in order to have all the newly created formulas resident when I change datasources to the correct tables.

However, when I do this the formulas do not copy. I have tried selecting "Entire PivotTable" and copy-pasting, and I have tried copying the entire tab by ctrl-drag(?) and then changing datasource to data2 (e.g.). Neither method works.

I have to believe there is something I'm missing on how replicate a pivot table and change the datasource without all the (new) calculations disappearing.

Any help is appreciated. thanks,

 

[Jerry Sullivan]

Ah, I hadn't understood until your last post that some but not all of the calculated fields were disappearing. So when I initially tested your workbook, I was focused on watching the calculated field "Average Price per Unit" that you noted in your OP. That field remained after changing the data source and since most the other fields were collapsed, I didn't notice it was only the fields in columns AF:AN that disappeared.

After some testing, I'm pretty sure that your speculation in Post #3 was spot on....

The odd part is that after I delete a pivot table and replace it with a new one, and I change the data source, the calculations that remain are those that were in the previous pivot table associated to this data source. There are two other pivot tables associated to each data pull in addition to the one I'm trying to replace. Could these be somehow taking precedence on the data source and limiting any new calculations on new sheets/pivot tables associated with the same source?

There's definitely a pattern that if "new" datasource (data2) for the copied Pivot is already being used as the datasource for another PivotTable, then the copied PivotTable will not inherit the Calculated Fields from the original Pivot (on Sheet1). What happens is that instead of creating a new PivotCache that uses data2 as its datasource, the copied PivotTable just references the existing PivotCache.

With that understanding, the workaround would entail ensuring that a new PivotCache is created for the copied Pivot when its datasource is changed. The easiest way to do that would be to delete all the PivotTables that are already using data2 as a datasource. That might work for your application since it sounds like you want to replace them all with the new Pivot.

If you had to retain the existing Pivots that already reference data2, you could do a sequence of temporarily remapping the existing Pivots to use a slightly different range (eg data2 + 1 row). After setting the datasource in the copied Pivot to data2, you could reset those other existing Pivots datasource to data and they should acquire the calculated fields associated with the newly created PivotCache for data2.

 

[Stepjack]

Jerry,
I'll try deleting the all pivot tables associated to the other data sources and then recopying from the first. This should clear the caches and make it all work.
Many thanks for all your help,
Steve