Error: PivotTable report cannot overlap another PivotTable Report

[osscie3]

Hey all. I've found where other people have had this error but those solutions haven't helped me solve this one.

Long story short I built this for another employee about a year ago and my memory is fuzzy on some specifics.

There's only one PivotTable on the sheet.

I get the typical error message when I hit refresh.

The weird part is that I can move around my fields, remove, add, etc except for one field. It looks like I created some sort of calculated field of a date field. It's named "Years" and it has a little filter icon beside it in the PivotTable Fields area. When I try to remove that field from the Rows section, I get the overlap error message again. Does the same thing when I try to move that field to a filter, value, or column area.

Any thoughts or ways I could troubleshoot this thing?

 

[RoryA]

Add this code to a new module in your workbook and tell us what the message says:

Sub HowManyPivots()
msgbox activesheet.pivottables.count
end sub

 

[osscie3]

Add this code to a new module in your workbook and tell us what the message says:

Sub HowManyPivots()
msgbox activesheet.pivottables.count
end sub

Just comes back with 1. Odd.

 

[RoryA]

Are there other pivot tables in the same workbook that use the same source data?

 

[osscie3]

Ok now we're on to something. When I delete all other pivot tables in the workbook (they're on separate sheets), I don't get the error anymore but the "Years" field goes away as well.

 

[RoryA]

Right click your date field in the pivot table and choose Group, then include Years in the grouping. That should bring back the Years field. Now you just need to figure out which of the other pivot tables was behind the error message. Did you have more than one pivot on any of your sheets?

 

[osscie3]

Figured out which sheet was causing it. For some reason though when I go back and try to Group by Year, it tells me "Cannot group that selection". Edit - It can group but it basically just puts all the data up under "Group1". Edit2: Got it to work finally. Had to do some data integrity checks.