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.
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.
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 overlapping Pivot tables 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.
The next thing I did was to select a cell in my workbook (shown in red below).
Then I switched to the Power Pivot window and selected “Refresh All”.
After the refresh, this is what I was in my worksheet.
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
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.
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.
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).
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.
I have tried and tried and I still cannot get the error to go away! so frustrated …. can someone please help me?
brilliant… thank you.
I’ve been using Excel 23 years, mouse free, lol.. and can’t believe MS hasn’t solved this one yet
Thanks for this excellent post and code!! Saved me a lot of time. Really appreciate it.
Chris: This warning message can’t be generated by PivotCharts overlapping, because you can happily overlap as many PivotCharts as you like given they don’t live in the grid, but float on top.
I suggest you use the code I posted at at http://dailydoseofexcel.com/archives/2017/12/08/identifying-pivottable-collisions/ to find them.
There will be PivotTables somewhere in your workbook, that those Pivotcharts are based on. You can’t have PivotCharts without PivotTables.
Thank you so much for your code Jeff! You really helped me sort out a Pivot table overlap issue and now I’ve saved your code as a module to use in the future again, if need be.
Hi, great help here to find the source but how do I correct the problem. I have a number of charts (no real pivots) which are causing the problem. Should I just ignore the notice?
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.
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.
That sounds like a good idea, and a job for Jeff Weir!
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
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…
Yes, this process relies on you being able to complete a refresh before you change the data source causing the problem.
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.
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…
Jeff! Please, could you share the evil genius code that works both for OLAP and for regular pivot tables that you mentioned? Thanks a lot!
Sure thing. You can find it at http://dailydoseofexcel.com/archives/2017/12/08/identifying-pivottable-collisions/
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
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.
Nice and usefull. Thank you.
ahh, that is clever, thank you for sharing
Terrific! Very clever. Thanks, as always, Matt!