If you see error messages when refreshing, this Excel macro lists pivot table details, and might help with your troubleshooting. The macro code is shown below, and there’s also a free workbook with the macro in it, that you can download.
Pivot Table Refresh Problems
Occasionally, if you refresh a pivot table, or use the Refresh All command, you’ll see a pivot table error message. For example, this message warns that a pivot table field name is not valid:
- “The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.”
Overlap Another Pivot Table
Another common pivot table error message warns about pivot table overlap problems.
- “A PivotTable report cannot overlap another PivotTable report.”
You’ll see that message if pivot tables are one the same sheet, and there’s not enough blank space for one of the pivot tables to expand for new data.
Finding the Problem Pivot Tables
Sometimes it’s easy to find and fix the problem pivot table, or its source data. But, in a big workbook, with lots of pivot tables, and different data sources, it can be tricky to pinpoint the problem.
You might be refreshing one pivot table, and it has plenty of room to grow, but the overlap error message appears. That can happen if other pivot tables, on other worksheets, use the same pivot cache, and they don’t have empty rows or columns beside them.
List Pivot Table Details
To get an inventory of all the pivot tables in your workbook, with details on where they’re located, use the macro shown below.
This video shows how the macro helps with trouble shooting, and you can get the sample file from my Contextures website, to follow along.
Macro Lists Pivot Table Details
As you saw in the video, the macro adds a sheet to your workbook, with details on all the pivot table. Columns E and F show if there are other pivot tables in the same columns or rows – that might help you find overlap problems.
In column L (Head Fix), an “X” will appear if the number of headings does not match the number of columns in the source data. That can help you find and fix the “field name not valid” problems.
The Macro Code
Here is the code for the macro. You can copy it to a regular code module in your workbook.
Sub ListWbPTsDetails()
Dim ws As Worksheet
Dim wsSD As Worksheet
Dim lstSD As ListObject
Dim pt As PivotTable
Dim rngPT As Range
Dim wsPL As Worksheet
Dim rngSD As Range
Dim rngHead As Range
Dim pt2 As PivotTable
Dim rngPT2 As Range
Dim rCols As Range
Dim rRows As Range
Dim RowPL As Long
Dim RptCols As Long
Dim SDCols As Long
Dim SDHead As Long
Dim lBang As Long
Dim nm As Name
Dim strSD As String
Dim strRefRC As String
Dim strRef As String
Dim strWS As String
Dim strAdd As String
Dim strFix As String
Dim lRowsInt As Long
Dim lColsInt As Long
On Error Resume Next
RptCols = 13
RowPL = 2
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
End Sub
Get the Pivot Table Macro Workbook
Get the sample workbook, with the troubleshooting macro, from the Excel Pivot Table List Macros page on my website.
The zipped file is in xlsm format, and contains macros. Enable macros when you open the workbook, if you want to test the code.
______________________
Excel Macro Lists Pivot Table Details
______________________