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:

11 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
    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

    MsgBox Join(sn, vbLf), , it.Name & " no overlap"
    End If
    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?

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

Leave a Reply

Your email address will not be published. Required fields are marked *