pivot tables seemed to be linked

[sthshrmags]

I have an excel workbook with multiple pivot tables all using the data sheet. When I group or ungroup items in one pivot table it changes all the others as well. How do I stop this? I just want each pivot table to be independent of each other while using the same data sheet. I tried recreating the pivot tables without copying an existing one but it just keeps happening. I have never seen this before. I cant find answers to this one when I search. Thank you.

[romperstomper]

That is the default behaviour in current versions of Excel. If you use Alt+DP to invoke the pivot table wizard, you can alter the source data so it does not use an existing pivot table's data as the source.

[sthshrmags]

Thank you Romper but my pivot tables are all using the same data sheet. They point to the same data sheet. Is there something else I can do?

[romperstomper]

You may need to recreate the pivots using the wizard - as long as you don't select another pivot table as the data source in step 1 you should get a separate cache.

[sthshrmags]

I've done that too. I am using the 1 data sheet for each pivot table. Not referring to an existing pivot. If I group some names together on one they group on the other. If I ungroup on one they ungroup on the other.

[romperstomper]

If you select your data (not an existing pivot table), then press Alt+DP to invoke the wizard (not Insert-Pivot table - that won't work) and specify the same data range as an existing pivot uses, you should get a message about sharing the cache. If you choose no, an additional cache will be created for each pivot table you create this way and groupings in one will not affect the other(s).

[romperstomper]

If you want to unlink your existing pivots rather than create new ones, you can try some code like this:

Please Login or Register  to view this content.

Please test on a copy of your workbook!