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.