Changing copied pivot table changes original?

[DianeJ]

Hi,

Please accept my apologies if this has been posted before, however I cannot find any other posts on this subject (or am using the wrong search terms)

I have a workbook FULL of pivot tables. To reduce the time required to create the workbook, after creating, formatting and tweaking the pivot tables and pivot charts in one worksheet I copied and pasted all on that sheet (by selecting all) to another worksheet. However I am finding that changing eg. the filter in a pivot table in worksheet 2 changes the pivot table from which it was copied in worksheet 1.

I would rather not have to create and format each pivot table and pivot chart from scratch if possible, is there any way to 'unlink' a copied pivot table / chart?

Thank you in advance... in despair, Diane

 

[ChrisM]

To be more efficient, whenever two pivot tables pull from the same data source it is better to base one pivot off the other. This is what has happened to you.

To fix, you need to set the data source on the second pivot to the original table of data.

Most likely if you go to the Pivot ribbon, choose Change Data Source, you will see it is pointing to the first pivot table. Change that to the actual raw data itself.

 

[DianeJ]

To be more efficient, whenever two pivot tables pull from the same data source it is better to base one pivot off the other. This is what has happened to you.

To fix, you need to set the data source on the second pivot to the original table of data.

Most likely if you go to the Pivot ribbon, choose Change Data Source, you will see it is pointing to the first pivot table. Change that to the actual raw data itself.

Hi, no, unfortunately checking the datasource of the 2nd pivot table reveals it to be targetting the same datasource (a worksheet of the raw data) as the first. But thank you for your thought...

 

[Hermanito]

Hey there,

please check this thread: http://www.mrexcel.com/forum/showthread.php?t=556372
where I explain in post #6 how to get two independent pivottables from the same sourcedata.

When you copy/paste a pivottable, it is based on the same pivotcache. Changing the pivotcache afterwards can only be done in VBA I think. So you should not make a copy/paste, but really create from scratch and choose the correct option in the messagebox, as described in the thread of the link above...

 

[LucyJ]

This worked for me! Thank you so much!

 

[LucyJ]

Actually, what worked was posted on another thread by Marcelo Branco. Here is what he said and it worked for me.

Create a PivotTable report that is based on the same cell range as another report without sharing the data cache
1.Ensure that there is an existing PivotTable report that is based on the same range that you want to use for the new PivotTable report.
2.Click any blank cell in the worksheet outside the PivotTable report.
3.To start the PivotTable and PivotChart Wizard, press ALT+D+P.

------------------------------------------------------------------------------------------
Tip
To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:
1.Click the arrow next to the toolbar, and then click More Commands.
2.Under Choose commands from, select All Commands.
3.In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.
--------------------------------------------------------------------------------

4.On the Step 1 page of the wizard, click Microsoft Office Excel list or database, and then click Next.
5.On the Step 2 page of the wizard, select the range of data on which you want to base the new PivotTable report, and then click Next.
6.When the PivotTable and PivotChart Wizard displays a message asking if you want to share the data cache, click No.
7.On the Step 3 page of the wizard, select a location for the new PivotTable report, and then click Finish.