Home > Pivot Table > Layout > Macros Pivot Table Troubleshoot MacrosFor troubleshooting Pivot Table refresh errors, use these macros to create a list of pivot tables in the active workbook, with key information about each pivot table. Also see: List all Pivot Fields with Details |
List All Pivot Table – HeadingsNOTE: This macro has been updated, and the new version is below, in the List All Pivot Tables - Details macro, below. All of the columns from the old Headings macro are included in the new Details macro, but the following heading labels are different in the new macro:
![]() |
List All Pivot Tables - DetailsUse this macro to create a list of all the pivot tables in the active workbook, with details on the source data, and possible conflicts with other pivot tables, or missing headings. This can help if you get an error message when refreshing pivot tables, such as:
OR this message:
This video shows how the macro helps with trouble shooting, and there are written details below the video. |
Pivot Table Details List MacroThe code checks each worksheet, and for each pivot table on that sheet, it lists the following information:
Also, if the source data is a list in the same Excel workbook, and not in the Data Model, this macro shows the following details about the source date:
|
Pivot Table Details List CodeAdd this macro to a regular code module in your workbook. NOTE: This is an improved version of "List All Pivot Table – Headings" Sub ListWbPTsDetails() Dim ws As Worksheet, wsSD As Worksheet Dim lstSD As ListObject Dim pt As PivotTable Dim rngPT As Range Dim wsPL As Worksheet Dim rngSD As Range, rngHead As Range Dim pt2 As PivotTable Dim rngPT2 As Range, rCols As Range Dim rRows As Range Dim RowPL As Long Dim RptCols As Long, SDCols As Long Dim SDHead As Long, lBang As Long Dim nm As Name Dim strSD As String, strRefRC As String Dim strRef As String, strWS As String Dim strAdd As String, strFix As String Dim lRowsInt As Long, lColsInt As Long Dim CountPT As Long On Error Resume Next RptCols = 13 RowPL = 2 For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables CountPT = CountPT + 1 If CountPT > 0 Then Exit For Next pt If CountPT > 0 Then Exit For Next ws If CountPT = 0 Then MsgBox "No pivot tables in this workbook" GoTo exitHandler End If Set wsPL = Worksheets.Add With wsPL .Range(.Cells(1, 1), .Cells(1, RptCols)).Value _ = Array("Worksheet", _ "Ws PTs", "PT Name", _ "PT Range", "PTs Same Rows", _ "PTs Same Cols", "PivotCache", _ "Source Data", "Records", _ "Data Cols", "Data Heads", _ "Head Fix", "Refreshed") End With For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables lRowsInt = 0 lColsInt = 0 Set rngPT = pt.TableRange2 For Each pt2 In ws.PivotTables If pt2.Name <> pt.Name Then Set rngPT2 = pt2.TableRange2 Set rRows = Intersect(rngPT.Rows.EntireRow, _ rngPT2.Rows.EntireRow) If Not rRows Is Nothing Then lRowsInt = lRowsInt + 1 End If Set rCols = Intersect(rngPT.Columns.EntireColumn, _ rngPT2.Columns.EntireColumn) If Not rCols Is Nothing Then lColsInt = lColsInt + 1 End If End If Next pt2 If pt.PivotCache.SourceType = 1 Then 'xlDatabase Set nm = Nothing strSD = "" strAdd = "" strFix = "" SDCols = 0 SDHead = 0 Set rngHead = Nothing Set lstSD = Nothing strSD = pt.SourceData 'worksheet range? lBang = InStr(1, strSD, "!") If lBang > 0 Then strWS = Left(strSD, lBang - 1) strRefRC = Right(strSD, Len(strSD) - lBang) strRef = Application.ConvertFormula( _ strRefRC, xlR1C1, xlA1) Set rngSD = Worksheets(strWS).Range(strRef) SDCols = rngSD.Columns.Count Set rngHead = rngSD.Rows(1) SDHead = WorksheetFunction.CountA(rngHead) GoTo AddToList End If 'named range? Set nm = ThisWorkbook.Names(strSD) If Not nm Is Nothing Then strAdd = nm.RefersToRange.Address SDCols = nm.RefersToRange.Columns.Count Set rngHead = nm.RefersToRange.Rows(1) SDHead = WorksheetFunction.CountA(rngHead) GoTo AddToList End If 'list object? For Each wsSD In ActiveWorkbook.Worksheets Set lstSD = wsSD.ListObjects(strSD) If Not lstSD Is Nothing Then strAdd = lstSD.Range.Address SDCols = lstSD.Range.Columns.Count Set rngHead = lstSD.HeaderRowRange SDHead = WorksheetFunction.CountA(rngHead) GoTo AddToList End If Next End If AddToList: If SDCols <> SDHead Then strFix = "X" With wsPL .Range(.Cells(RowPL, 1), _ .Cells(RowPL, RptCols)).Value _ = Array(ws.Name, _ ws.PivotTables.Count, _ pt.Name, _ pt.TableRange2.Address, _ lRowsInt, lColsInt, _ pt.CacheIndex, pt.SourceData, _ pt.PivotCache.RecordCount, _ SDCols, SDHead, strFix, _ pt.PivotCache.RefreshDate) 'add hyperlink to pt range .Hyperlinks.Add _ Anchor:=.Cells(RowPL, 4), _ Address:="", _ SubAddress:="'" & ws.Name _ & "'!" & pt.TableRange2.Address, _ ScreenTip:=pt.TableRange2.Address, _ TextToDisplay:=pt.TableRange2.Address End With RowPL = RowPL + 1 Next pt Next ws With wsPL .Rows(1).Font.Bold = True .Range(.Cells(1, 1), .Cells(1, RptCols)) _ .EntireColumn.AutoFit End With exitHandler: Set wsPL = Nothing Set ws = Nothing Set pt = Nothing Exit Sub End Sub |
List Sheets with Multiple Pivot TablesUse the following macro, named ListWbPTsMulti, to create a list of all the pivot tables in the active workbook, for sheets that have 2 or more pivot tables. First, the macro counts the sheets that have 2 or more pivot tables.
If at least one sheet has multiple pivot tables, the macro adds a new sheet to the workbook. On that sheet, the macro creates a list of pivot tables, with the following information:
|
Pivot List Macro CodeAdd this ListWbPTsMulti macro to 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 Dim strHid As String On Error Resume Next '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 Application.EnableEvents = False Set wsPL = Worksheets.Add lCols = 8 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", "Hidden") r = 2 For Each ws In ActiveWorkbook.Worksheets lPTs = ws.PivotTables.Count If lPTs > 1 Then Select Case ws.Visible Case -1: strHid = "Visible" Case 0: strHid = "Hidden" Case 2: strHid = "Very Hidden" End Select 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, strHid) '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 |
Pivot Table Information MessageIf you only need to troubleshoot one pivot table, instead of all the pivot tables in a workbook, the following macro might help you get started. Select any cell in the pivot table that you want to check, and then run the following macro. It shows a message box, with general details about the pivot table. Tip: In the sample workbook, click the worksheet button to run the macro. |
Macro Code for Pivot Info MessageHere is the macro code, and you can also get this code in the sample workbook Sub SelectedPTInfoMsg() Dim pt As PivotTable Dim pc As PivotCache Dim ws As Worksheet Dim strInfo As String Dim strOld As String Dim strST As String Dim strSource As String Dim strMem As String On Error GoTo errHandler Set ws = ActiveSheet If ws.PivotTables.Count = 0 Then MsgBox "There are no pivot tables" _ & vbCrLf _ & "on the active sheet" GoTo exitHandler End If On Error Resume Next Set pt = ActiveCell.PivotTable If pt Is Nothing Then MsgBox "Please select a pivot table cell" GoTo exitHandler Else Set pc = pt.PivotCache Select Case pc.MissingItemsLimit Case -1 strOld = "Default" Case 0 strOld = "None" Case Is > 0 strOld = "Max" End Select Select Case pc.SourceType Case 1 strSource = pt.SourceData strST = "xlDatabase" Case 2 strSource = "External" strST = "xlExternal" Case 3 strSource = "Consolidation" strST = "xlConsolidation" Case 4 strSource = "Scenario" strST = "xlScenario" Case -4148 strSource = "another PivotTable" strST = "xlPivotTable" End Select strInfo = strInfo _ & " Name: " _ & pt.Name strInfo = strInfo & vbCrLf strInfo = strInfo _ & " Address: " _ & ws.Name _ & "!" _ & pt.TableRange2.Address _ & " " strInfo = strInfo _ & vbCrLf & vbCrLf strInfo = strInfo _ & " Source Type: " _ & strST strInfo = strInfo & vbCrLf strInfo = strInfo _ & " Source Data: " _ & strSource strInfo = strInfo & vbCrLf strInfo = strInfo _ & " Records: " _ & pc.RecordCount strInfo = strInfo _ & vbCrLf & vbCrLf strInfo = strInfo _ & " Cache Index: " _ & pt.CacheIndex strInfo = strInfo & vbCrLf strMem = pc.MemoryUsed strInfo = strInfo _ & " Cache Memory: " _ & Format(strMem / 1000, "0") _ & " kb " strInfo = strInfo & vbCrLf strInfo = strInfo _ & " Retain Old Items: " _ & strOld strInfo = strInfo & vbCrLf & vbCrLf strInfo = strInfo _ & " Last Refresh: " _ & pt.RefreshDate & " " strInfo = strInfo & vbCrLf strInfo = strInfo _ & " By: " _ & pt.RefreshName MsgBox strInfo End If exitHandler: Set pt = Nothing Set ws = Nothing Exit Sub errHandler: GoTo exitHandler End Sub |
Get the WorkbooksPivot Table Lists: To see how the macros work, and to get the sample code, download the Pivot Table List Macros workbook. The zipped file is in xlsm format, and contains macros. Enable macros when you open the workbook, if you want to test the macros. Pivot Info Message: To see how the pivot table information message macro works, and to get the sample code, download the Pivot Table Info Message workbook. The zipped file is in xlsm format, and contains one macro. Enable macros when you open the workbook, if you want to test the macro. More Tutorials |
Last updated: January 10, 2023 9:40 AM