How to Find Pivot Table Refresh Overlap Problem

When you try to refresh an Excel pivot table, you might see an error message that warns you about an overlap problem: “A PivotTable report cannot overlap another PivotTable report.” To help you find the pivot table that is causing the problem, use this macro. It lists all the pivot tables where there are two or more on the same worksheet.

Refresh a Pivot Table

If you’re refreshing a pivot table that’s the only thing on a worksheet, it’s confusing when an error message warns you about an overlap, like the one shown below.

However, when you refresh a pivot table, Excel automatically refreshes all other pivot tables that use the same pivot cache.

Those other pivot tables might be anywhere in the workbook, even on hidden sheets.

In a large workbook, it could be hard to find those pivot tables, so I’ve created a macro to make that task easier.

Video: Fix Excel Pivot Table Refresh Errors

This short video shows the problems, and how to avoid them. For detailed written notes, go to the Pivot Table Errors page on my Contextures site.

List Pivot Tables With Possible Overlap

To quickly find pivot tables that might have an overlap problem, use the macro shown below. This macro creates a list of all the pivot tables in the active workbook, for sheets that have 2 or more pivot tables.

First, the code counts the sheets that have 2 or more pivot tables.

• If no sheets with 2 or more pivot tables are found, the macro ends.

If at least one sheet has multiple pivot tables, the macro adds a new sheet to the workbook.

On that new sheet, the macro creates a list of pivot tables, with the following information:

• Worksheet name
• Number of pivot tables on the sheet
• Pivot Table name
• Number of columns and rows in the pivot table
• Pivot Table address (with hyperlink so you can check it easily)
  • Hyperlink works if sheet is visible
• Pivot Cache index number
• Sheet Visibility – Visible, Hidden, or Very Hidden

List Pivot Tables – Macro

Here is the code for the ListWbPTsMulti macro. Copy it from here, or get the code from the sample file (link in next section).

Then, paste this ListWbPTsMulti macro code in a regular code module in your workbook.

Sub ListWbPTsMulti()
Dim ws As Worksheet
Dim pt As PivotTable
Dim ptTR2 As Range
Dim wsPL As Worksheet
Dim r As Long
Dim ptAddr As String
Dim lWks As Long
Dim lPTs As Long
Dim lColsPT As Long
Dim lColHL As Long
Dim lRowsPT As Long
Dim lCols As Long
On Error Resume Next
Application.EnableEvents = False

'check for multi pt sheets
For Each ws In ActiveWorkbook.Worksheets
  If ws.PivotTables.Count > 1 Then
    lWks = lWks + 1
  End If
Next ws

If lWks = 0 Then
  MsgBox "No sheets have multiple pivot tables"
  GoTo exitHandler
End If

Set wsPL = Worksheets.Add
lCols = 7
lColHL = 6 'column with hyperlink

wsPL.Range(wsPL.Cells(1, 1), _
      wsPL.Cells(1, lCols)).Value = _
    Array("Worksheet", "PTs", _
      "PT Name", "PT Cols", _
      "PT Rows", "Address", "Cache")
r = 2

For Each ws In ActiveWorkbook.Worksheets
  lPTs = ws.PivotTables.Count
  If lPTs > 1 Then
   For Each pt In ws.PivotTables
   Set ptTR2 = pt.TableRange2
   ptAddr = Replace(ptTR2.Address, "$", "")
     
    lColsPT = pt.TableRange2.Columns.Count
    lRowsPT = pt.TableRange2.Rows.Count
     
     wsPL.Range(wsPL.Cells(r, 1), _
      wsPL.Cells(r, lCols)).Value = _
        Array(ws.Name, lPTs, _
          pt.Name, lColsPT, lRowsPT, _
          ptAddr, pt.CacheIndex)
        'add hyperlink to pt address
        wsPL.Hyperlinks.Add _
            Anchor:=wsPL.Cells(r, lColHL), _
            Address:="", _
            SubAddress:="'" & ws.Name & "'!" _
                & ptAddr, _
            ScreenTip:=pt.Name, _
            TextToDisplay:=ptAddr
     r = r + 1
   Next pt
  End If
Next ws

With wsPL
  .Tab.ColorIndex = 16777215  'white
  .Rows(1).EntireRow.Font.Bold = True
  .Cells(1, 1).CurrentRegion.EntireColumn.AutoFit

  .ListObjects.Add(xlSrcRange, _
    .Cells(1, 1).CurrentRegion, , xlYes) _
      .Name = ""
End With

exitHandler:
  Set pt = Nothing
  Set ptTR2 = Nothing
  Set ws = Nothing
  Set wsPL = Nothing
  Application.EnableEvents = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

Get the Workbook

To see more macros that list pivot table details, and to get the sample Excel workbook, go to the Pivot Table List Macros page on my Contextures site.

There are pivot cache macros too, that you might find helpful. For those, go to the Excel Pivot Cache page.

______________________

How to Find Pivot Table Refresh Overlap Problem

_____________________