Identifying PivotTable Collisions

Posted on December 8, 2017December 8, 2017 by Jeff Weir

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:

Sub FindPivotOverlaps()

Dim ws As Worksheet
Dim pt              As PivotTable
Dim pt2             As PivotTable
Dim lo              As ListObject
Dim rOffset         As Range
Dim cell            As Range
Dim sMsg            As String


For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        With pt.TableRange2
            Set rOffset = Union( _
                                .Offset(pt.TableRange2.Rows.Count, 0).Resize(1), _
                                .Offset(0, pt.TableRange2.Columns.Count).Resize(pt.TableRange2.Rows.Count, 1))
        End With
        'Test for ListObject collision
        Set lo = Nothing
        On Error Resume Next
        Set lo = rOffset.ListObject
        On Error GoTo 0
        If Not lo Is Nothing Then
            sMsg = sMsg & lo.Name & vbTab & "'" & lo.Parent.Name & "'!" & lo.DataBodyRange.Address & vbNewLine
        Else
            'Test for PivotTable collision
            For Each cell In rOffset
                Set pt2 = Nothing
                On Error Resume Next
                Set pt2 = cell.PivotTable
                On Error GoTo 0
                If Not pt2 Is Nothing Then
                    sMsg = sMsg & pt2.Name & vbTab & "'" & pt2.Parent.Name & "'!" & pt2.TableRange2.Address & vbNewLine
                    Exit For
                End If
            Next cell
        End If
        
    Next pt
Next ws

If sMsg = "" Then sMsg = "No overlaps found!"
MsgBox sMsg

End Sub

Sub FindPivotOverlaps()

Dim ws As Worksheet

Dim pt As PivotTable

Dim pt2 As PivotTable

Dim lo As ListObject

Dim rOffset As Range

Dim cell As Range

Dim sMsg As String

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

With pt.TableRange2

Set rOffset = Union( _

.Offset(pt.TableRange2.Rows.Count, 0).Resize(1), _

.Offset(0, pt.TableRange2.Columns.Count).Resize(pt.TableRange2.Rows.Count, 1))

End With

'Test for ListObject collision

Set lo = Nothing

On Error Resume Next

Set lo = rOffset.ListObject

On Error GoTo 0

If Not lo Is Nothing Then

sMsg = sMsg & lo.Name & vbTab & "'" & lo.Parent.Name & "'!" & lo.DataBodyRange.Address & vbNewLine

Else

'Test for PivotTable collision

For Each cell In rOffset

Set pt2 = Nothing

On Error Resume Next

Set pt2 = cell.PivotTable

On Error GoTo 0

If Not pt2 Is Nothing Then

sMsg = sMsg & pt2.Name & vbTab & "'" & pt2.Parent.Name & "'!" & pt2.TableRange2.Address & vbNewLine

Exit For

End If

Next cell

End If

Next pt

Next ws

If sMsg = "" Then sMsg = "No overlaps found!"

MsgBox sMsg

End Sub

…which does this:

31 thoughts on “Identifying PivotTable Collisions”

martin says:

February 20, 2018 at 10:55 am

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
Jeff Weir says:

February 21, 2018 at 12:51 pm

Hi Martin. Can you elaborate further about the nature of the issues?
snb says:

February 22, 2018 at 8:45 am

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
Dag Aksnes says:

July 2, 2018 at 3:47 am

Truly a life saver. Thx a lot!
IA says:

October 24, 2018 at 5:36 am

Thank you, this was really a life saver.
Evelyn Wong says:

November 14, 2018 at 3:19 am

can you teach me how to run the code? step by step.

Thanks in advanced.
Jeff Weir says:

November 14, 2018 at 12:19 pm

Hi Evelyn. I wrote a blog post some time back over at the Chandoo blog that has step-by-step instructions. See https://chandoo.org/wp/using-personal-macro-workbook/
Mirko says:

December 6, 2018 at 1:29 am

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?
Mirko says:

December 6, 2018 at 1:33 am

SNB?
snb reports sintax error in my vb editor
Jeff Weir says:

December 9, 2018 at 7:23 pm

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.
Sarah says:

April 3, 2019 at 10:43 am

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?
Ryan O'Keefe says:

June 4, 2019 at 8:11 am

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

August 1, 2019 at 4:14 pm

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!
Agustin says:

August 1, 2019 at 4:38 pm

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

September 10, 2019 at 6:02 am

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?
Jeff Weir says:

September 12, 2019 at 2:34 pm

@Johnsen Can you share your file with me?
Jeremy says:

September 18, 2019 at 9:51 am

Pro tip — this will not function on hidden worksheets. Otherwise very helpful!
Johnsen says:

September 20, 2019 at 4:51 am

@Jeff Weir – Sorry no. I hope you could help anyway.
Jono says:

September 24, 2019 at 6:00 am

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.
pernette says:

October 4, 2019 at 11:54 am

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!
Jeff Weir says:

October 14, 2019 at 1:38 am

Thanks Pernette :-)
Pelly says:

January 20, 2020 at 4:49 am

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!
JK says:

January 22, 2020 at 12:01 pm

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.
ZCO says:

February 21, 2020 at 3:17 am

Excellent….was scratching my head over this one for a while
Palesa says:

May 14, 2020 at 5:32 am

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?
Daniele P says:

February 19, 2021 at 8:24 am

I have same situation of mirko jk and palesa. no foud messag but error on refrash
Amandeep says:

May 5, 2021 at 10:53 am

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?
Maryann says:

July 1, 2021 at 5:50 pm

You are brilliant and a lifesaver. THANK YOU!!!
Aaron says:

September 7, 2021 at 6:14 pm

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

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

Sub M_snb()
    Dim it As Object
    Dim j As Long, jj As Long
    Dim c00 As Range
    
    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

Sub M_snb()

Dim it As Object

Dim j As Long, jj As Long

Dim c00 As Range

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

pero says:

September 23, 2021 at 7:30 am

If (Not Intersect(Range(sn(jj)), pp) Is Nothing) Then

Method intersect of object _Global failed

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

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Identifying PivotTable Collisions

31 thoughts on “Identifying PivotTable Collisions”

Leave a Reply