How to Find Overlapping Pivot Tables

A client contacted me recently and asked me to solve a problem for them.  They have a large Power Pivot Excel workbook with lots of Pivot Tables.  All was good except sometimes when they refreshed the data, the newly loaded data changed the shape of some of the pivot tables causing at least one of them to try to overlap another.  Take the following example.

image

There are 4 pivot tables in the above workbook.  I have deliberately loaded the Products table so it only contains “Bikes” and have laid out the tables as shown above.  Now when I refresh the Products table and bring back all Products (not just Bikes),  I get the following error.

image

Note the annoying question at the bottom “Was this information helpful?”.  No actually, it is not helpful at all.  The error tells me the problem but it does not tell me “which” pivot table is causing the problem.  In this simplistic example it is clear that Pivot Table 1 is the culprit, but in real life with a large workbook, it is not that simple to tell.

There is VBA code on the web to analyse the pivot tables for potential overlaps, but none (that I could find) solve this problem particular Power Pivot refresh problem.  Why?  Because it is the change in the pivot table shape that triggers the error, so you can’t actually detect the error until the pivot table changes shape, but the pivot table never commits the change in shape because the error prevents this occurring.  So what to do?

Create Some Auditing VBA

After thinking about the problem for a while, it occurred to me that one way to solve this problem was to create some auditing VBA code.  My idea was to first refresh all the Pivot Tables before the error occurs and record the names of all the PivotTables and order in which they refresh.  Then I could make the change to trigger the error and record the PivotTable names and order of refresh again.  I can then work out what is different hence uncovering the culprit.  Let me demonstrate

Here is my VBA code.  Note if you don’t know how to copy this VBA code, read my article here.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
    ActiveCell = Target.Name
    ActiveCell.Offset(1, 0).Select
 
End Sub

Simply place this code in the Worksheet Code Page in the Visual Basic editor as shown below.  You will need to add the code on every sheet that has pivot tables.

image

The next thing I did was to select a cell in my workbook (shown in red below).

image

Then I switched to the Power Pivot window and selected “Refresh All”.

image

After the refresh, this is what I was in my worksheet.

image

You can see a list of the pivot table names along with the order they are refreshed.  Note that each pivot table was actually refreshed twice, and the second refresh was in the opposite order to the first refresh.

I then selected the cell to the right of the list as shown below

image

Then I loaded the new data from my data source in the Power Pivot Window.  It is the new data that will trigger the error for my demo.  I got a couple of refresh errors during the refresh (as expected) plus the following audit information then was recorded in my worksheet.

image

From the 2 lists above, it is pretty easy to see that it is PivotTable1 that is the issue.  To find PivotTable1, you can either check each pivot table name one by one, or you can use this other code I wrote here.

Sub gotoPivot()
    TableName = ActiveCell.Value
    For Each Sheet In Sheets
        On Error Resume Next
        x = Sheet.Name
        Y = Sheet.PivotTables(TableName).TableRange1.Cells(1).Address
        If Not IsEmpty(Y) Then GoTo ExitHere:
    Next Sheet
ExitHere:
    Application.Goto Reference:=Sheets(x).Range(Y)
End Sub

Simply click in the cell that contains that Pivot Table you are looking for as shown in 1 below.

image

Then run the VBA Code, and you will be taken directly to the culprit table.

What If I Need This Regularly?

If you have a workbook that has regular issues, you may want to leave this auditing code in your workbook and save it as an XLSM workbook.  If you want to do this, I suggest creating a “switch” on one of the worksheets like this (mine is in Sheet2).

image

I have renamed cell A1 above and given this cell a RangeName = Audit.  Then change the VBA code as follows.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
     If Sheets(2).Range("Audit").Value = "Yes" Then
        ActiveCell = Target.Name
        ActiveCell.Offset(1, 0).Select
     End If
End Sub

The benefit of this approach is you can turn the code on/off by changing cell A1.

I hope you find this useful.

Share?

Comments

  1. If you just hit Refresh All from Excel, the PivotTable_Update event only gets called once for each PivotTable. The fact that they get refreshed twice when you hit Refresh All from the PowerPivot window is weird. Bug, maybe?

    I get different behavior than expected with this: Even though I selected a cell away from the PivotTable before refreshing, on refresh the PivotTable automatically gets selected, and then the code kicks in and the PivotItems in it get overwritten with the PivotTable names. Which is messy. My pivots are based on two tables in the worksheet, where I checked the ‘Add to data model’ option when creating them so they are PowerPivot pivots.

    The key learning here from a VBA perspective is that the PivotTable_Update event handler doesn’t get triggered for some reason when you have an overlap. Which is handy, because it gives you a convenient way to find the culprit.

    Instead of writing to the range, I’d put something like this in a standard code module:
    Option Explicit
    Global dic As Object
    Sub RefreshPivots()
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim sMsg As String

    Set dic = Nothing
    Set dic = CreateObject(“Scripting.Dictionary”)
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
    dic.Add pt.Name & “: ” & pt.Parent.Name & “!” & pt.TableRange2.Address, 1
    Next pt
    Next ws

    ThisWorkbook.RefreshAll
    Application.DisplayAlerts = True
    If dic.Count > 0 Then
    sMsg = “The following PivotTable(s) are overlapping something: ”
    sMsg = sMsg & vbNewLine & vbNewLine
    sMsg = sMsg & Join(dic.keys, vbNewLine)
    MsgBox sMsg
    End If
    End Sub

    …and then I’d put this in the ThisWorkbook module:
    Option Explicit

    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    If dic.Count > 0 Then dic.Remove Target.Name & “: ” & Target.Parent.Name & “!” & Target.TableRange2.Address
    End Sub

    …so that whenever you run that RefreshPivots macro, you’ll get a messagebox giving you the names and addresses of the culprits.

    Nice find, Matt.

  2. Whoops, code revision needed.
    This goes in a standard code module, and is the routine you run when you want to find overlapping PivotTables. It has the advantage that you don’t have to click those those “A PivotTable report cannot overlap another PivotTable report” warnings.

    Option Explicit
    Global dic As Object
    Sub RefreshPivots()
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim sMsg As String

    Set dic = Nothing
    Set dic = CreateObject(“Scripting.Dictionary”)
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
    dic.Add pt.Name & “: ” & pt.Parent.Name & “!” & pt.TableRange2.Address, 1
    Next pt
    Next ws

    ThisWorkbook.RefreshAll
    Application.DisplayAlerts = True
    If dic.Count > 0 Then
    sMsg = “The following PivotTable(s) are overlapping something: ”
    sMsg = sMsg & vbNewLine & vbNewLine
    sMsg = sMsg & Join(dic.keys, vbNewLine)
    MsgBox sMsg
    End If
    Set dic = Nothing
    End Sub

    …and this goes in the ThisWorkbook module:

    Option Explicit

    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    If Not dic Is Nothing Then dic.Remove Target.Name & “: ” & Target.Parent.Name & “!” & Target.TableRange2.Address
    End Sub

  3. Unfortunately this approach will only work with OLAP pivots. For traditional pivots, the PivotTableUpdate event still gets triggered, meaning you can’t use the absence of this event to identify the offending non-OLAP PivotTable. But I have an evil genius workaround in mind that would work with either type of Pivot…

  4. It’s a good approach, but I have a question. You are executing two times the refresh. The first one is to get the refreshing pivot table order. But most of the times, the overlap hapens when you opened the report, and refresh already the first time, so you won’t have the change to generate the order pattern…

    • Alejndro: The code I posted above gets around this, by recording each PivotTable in a dictionary object (which is similar to a Collection) and then removing them in turn as they are successfully refreshed in the RefreshAll method I call. Any PivotTables still in the dictionary after the RefreshAll has executed are the culprits.

  5. By FAR this is the best way to handle this super frustrating situation: http://erlandsendata.no/?p=3733.
    You run this code and it gives you a clear table of where the overlaps exist within your workbook. In my mind this is basic functionality that just needs to exist in excel. Should work for no OLAP connections as well

    Function GetPivotTableConflicts(wb As Workbook) As Collection
    ‘ returns a collection with information about pivottables that overlap or intersect each other
    Dim ws As Worksheet, i As Long, j As Long, strName As String
    If wb Is Nothing Then Exit Function

    Set GetPivotTableConflicts = New Collection
    With wb
    For Each ws In .Worksheets
    With ws
    strName = “[” & .Parent.Name & “]” & .Name
    Application.StatusBar = “Checking PivotTable conflicts in ” & strName & “…”
    If .PivotTables.Count > 1 Then
    For i = 1 To .PivotTables.Count – 1
    For j = i + 1 To .PivotTables.Count
    If OverlappingRanges(.PivotTables(i).TableRange2, .PivotTables(j).TableRange2) Then
    GetPivotTableConflicts.Add Array(strName, “Intersecting”, _
    .PivotTables(i).Name, .PivotTables(i).TableRange2.Address, _
    .PivotTables(j).Name, .PivotTables(j).TableRange2.Address)
    Else
    If AdjacentRanges(.PivotTables(i).TableRange2, .PivotTables(j).TableRange2) Then
    GetPivotTableConflicts.Add Array(strName, “Adjacent”, _
    .PivotTables(i).Name, .PivotTables(i).TableRange2.Address, _
    .PivotTables(j).Name, .PivotTables(j).TableRange2.Address)
    End If
    End If
    Next j
    Next i
    End If
    End With
    Next ws
    Set ws = Nothing
    Application.StatusBar = False
    End With
    If GetPivotTableConflicts.Count = 0 Then Set GetPivotTableConflicts = Nothing
    End Function

    Function OverlappingRanges(objRange1 As Range, objRange2 As Range) As Boolean
    OverlappingRanges = False
    If objRange1 Is Nothing Then Exit Function
    If objRange2 Is Nothing Then Exit Function

    If Not Application.Intersect(objRange1, objRange2) Is Nothing Then
    OverlappingRanges = True
    End If
    End Function

    Function AdjacentRanges(objRange1 As Range, objRange2 As Range) As Boolean
    AdjacentRanges = False
    If objRange1 Is Nothing Then Exit Function
    If objRange2 Is Nothing Then Exit Function

    With objRange1
    If .Top + .Height = objRange2.Top Then
    AdjacentRanges = True
    End If
    If .Left + .Width = objRange2.Left Then
    AdjacentRanges = True
    End If
    End With
    With objRange2
    If .Top + .Height = objRange1.Top Then
    AdjacentRanges = True
    End If
    If .Left + .Width = objRange1.Left Then
    AdjacentRanges = True
    End If
    End With
    End Function
    The macro below shows how you can use the functions above.
    Sub ShowPivotTableConflicts()
    ‘ creates a list with all pivottables in the active workbook that conflicts with each other
    Dim coll As Collection, i As Long, varItems As Variant, r As Long
    If ActiveWorkbook Is Nothing Then Exit Sub

    Set coll = GetPivotTableConflicts(ActiveWorkbook)
    If coll Is Nothing Then
    MsgBox “No PivotTable conflicts in the active workbook!”, vbInformation
    Else
    Workbooks.Add ‘ create a new workbook
    Range(“A1”).Formula = “Worksheet:”
    Range(“B1”).Formula = “Conflict:”
    Range(“C1”).Formula = “PivotTable1:”
    Range(“D1”).Formula = “TableAddress1:”
    Range(“E1”).Formula = “PivotTable2:”
    Range(“F1”).Formula = “TableAddress2:”
    Range(“A1”).CurrentRegion.Font.Bold = True
    r = 1
    For i = 1 To coll.Count
    r = r + 1
    varItems = coll(i)
    Range(“A” & r).Formula = varItems(0)
    Range(“B” & r).Formula = varItems(1)
    Range(“C” & r).Formula = varItems(2)
    Range(“D” & r).Formula = varItems(3)
    Range(“E” & r).Formula = varItems(4)
    Range(“F” & r).Formula = varItems(5)
    Next i
    Range(“A1”).CurrentRegion.EntireColumn.AutoFit
    Range(“A2”).Select
    ActiveWindow.FreezePanes = True
    Range(“A1”).Select
    End If
    End Sub

  6. Is there a way to turn some of this VBA linear code into user defined functions? Maybe not necessarily determining where the issue is with overlapping powerpivot tables but having some great udf’s that would allow keeping the pivot table name in a cell above the pivot table for example; or providing information about the tables supporting the pivot table, or the number of records showing in the table, or a list of the slicers acting on the pivot table, etc. Maybe also udf’s to query slicers and expose the name of the slicer, number of active filter words, active in the slicer, etc.

  7. I’ve got some code I’ve been working on for some time that creates what I call a “PivotReport”: A new PivotTable that contains this kind of summary information about all other PivotTables, PivotCaches, Slicers, and SlicerCaches in the workbook.

    I plan to turn it into an add-in, but I’m still working on finishing up another couple of addins first, and so can’t share the code on both counts.

Leave a Reply

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

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x