Identifying PivotTable Collisions

So you’ve got a file with dozens of PivotTables in it. One day you hit Refresh All, only to see this complaint:

…or perhaps this variant:

What’s happened is that the size of the PivotTable has increased, and the PivotTable is now trying to occupy space where another Pivot or Excel Table already lives. So you know what has gone wrong. But you have no idea where. You’re either going to need to eyeball each and every bit of each and every worksheet (including the hidden ones), or you’re going to have to run a bit of code. Something like this:

…which does this:

31 thoughts on “Identifying PivotTable Collisions

  1. Thanks! I’m still having some issues when refreshing pivots that come from olap sources (when changing the data source options), but this works for most of the other cases!

    Best regards

  2. Sub M_snb()
    For Each it In Sheets
    ReDim sn(it.ListObjects.Count + it.PivotTables.Count - 1)
    If UBound(sn) > -1 Then
    sn(0) = IIf(it.ListObjects.Count > 0, it.ListObjects(1).Range.Address, it.PivotTables(1).TableRange2.Address)

    For j = 2 To UBound(sn) + 1
    If j it.ListObjects.Count Then Set c00 = it.PivotTables(j - it.ListObjects.Count).TableRange2
    For jj = 0 To UBound(sn)
    If IsEmpty(sn(jj)) Then Exit For
    If Not Intersect(Range(sn(jj)), c00) Is Nothing Then Exit For
    Next
    If IsEmpty(sn(jj)) Then
    sn(jj) = c00.Address
    ElseIf jj <= UBound(sn) Then
    MsgBox "Overlap " & sn(jj) & vbTab & c00.Name, , it.Name
    Exit Sub
    End If
    Next

    MsgBox Join(sn, vbLf), , it.Name & " no overlap"
    End If
    Next
    End Sub

  3. Identifying PivotTable Collisions
    Posted on December 8, 2017 by Jeff Weir

    I copyed code and it works! Said “No overlaping found”! I was happy, but when I refreshed my pivots, I got a same massage “Pivot can’t overlap another pivot…”.

    I also checked manualy all my 50 sheets – and there is no overlaping, but after transition to office 365, I keep goting this error on refresh.

    What to do?

  4. Mirko…can you share you file with me so I can take a look? If lo, let me know and I’ll email you directly.

  5. I have a file that has a bunch of pivot tables. When I hit Refresh All I get the overlap error plus an additional error “A PivotTable, cube function or filter control using connection ‘Connection 321’ didn’t refresh. Continue to refresh all?” which causes none of the pivot tables to refresh at all. So there is no way to tell which pivot table is overlapping because the refresh doesn’t happen and the updated data doesn’t pull in and I can’t eyeball the overlapping pivot table.

    Is there a fix to this?

  6. The code plugged-in easy and worked PERFECTLY the first time! Thank you Jeff! You saved the day. :)

  7. Thank you so much Jeff – I’m afraid I also have the issue of getting the overlap error in Office 365, but no overlap that I can see or after running your code.
    Could you help please? Very much appreciated!

  8. Never mind Jeff. Luckily, it occurred to me to delete a couple of pivot tables and those were causing the glitch…somehow!
    Best,

  9. I have the same issue as Mirko above.. “No overlaping found” but still the error “Pivot can’t overlap another pivot…”.

    @Jeff Wier – Did you find a solution?

  10. Just thought I’d add an extra note in case anyone else experiences the error code and its not as a result of any overlapping pivots.

    Because I require my pivottables in a report to always have consistent column and row headers I have a hidden static table and an input table (with the same column headings) which receives data pasted in by users. These are then combined using an append query (within Excel Queries & Connections option) which my pivottables then refresh from, rather than from just the single user input table.

    During testing, I experienced if the user input table is empty when attempting to refresh a pivottable this error code is encountered. As soon as 1 line of dummy data is added to the user input table the error code no longer results.

  11. Thank you soooooo much!!!

    It’s almost 7pm on a friday night and your macro worked perfectly which means I can finish my report and enjoy my weekend :D

    you’re a true hero!

  12. Hey, thanks so much, the code seems to work great (I think)! However, I now get this message:

    Table57 ‘Stats’!$A$14:$Y$20

    How do I know which table is number 57?! Sorry, I am a bit of a novice!

  13. Hey,

    This doesn’t work when you click on refresh data. It works only when the sheets are already set. When I run this code, it shows ‘ No overlaps found’. But, when I refresh the whole sheet, I see the same error. Please help me out. This is really frustrating.

  14. Hi all
    I am experiencing the same issue as Mirko and JK ,
    I then moved the table to ensure no overlaps but still get the error.

    Has anybody found a solution to this?

  15. Thanks so much for this however I am getting the same error msg as those using office 365. did anybody find a solution to this?

  16. I am getting the same syntax error on Sub M_snb.
    What was the fix on this line of code:

    If j it.ListObjects.Count Then Set c00 = it.PivotTables(j – it.ListObjects.Count).TableRange2

  17. I’m guessing that was a ‘not equal to’ that WordPress thinks was an html tag


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.