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:

4 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

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 *