Overlapping pivot error when on new sheet

[D4WNO]

Hi,

I hope you can help, I've searched and I cannot quite find the same issue that I'm seeing in Excel.

I have lots of pivots linked to the same dataset, held on it's own sheet within the same file.

I'm simply trying to expand a date field with the ungroup option, but when I do it says "Excel was unable to update the pivot table named PivotTable1 because it would have overlapped another PivotTable".

Now this pivot is actually called Pivot9 and I've tried copying it into a fresh sheet and I still get exactly the same error, does anyone have any ideas please? Pivot 1 that it is referring to is on another sheet entirely and I've even tried deleting number 1 and the same still happens, so I'm very confused.#

Update - Tried pasting the pivot into a fresh workbook and it works, but I really need it all in the same doc

 

[Alex Blakenburg]

What is most likely the case is that a number of your pivots share the same Pivot Cache. Doing this saves space.
However what this means is that they are all refreshed at the same time when you refresh one of the Pivot tables.
It also means that any changes you make to Grouped Fields and Calculated Items affect all those using that same cache, since these are stored at the Cache level.

So it is quite likely that the change in grouping impacted Pivot Table 1 and then it is now trying to expand and running out of room.

If you need more information and how to go about changing your data source and change the Cache from your pivot including macro some options, you might like might find this page on Debra's Contextures site helpful Excel Macros for Pivot Table Pivot Cache - Lists, Info