Search Contextures Sites 
Excel VBA Pivot Table Field Info List
Create List of Pivot Table Fields
Create List of Pivot Fields By Location
Pivot Table Tutorials and Videos
Create a list of the fields in a pivot table, along with information about each field, such as the caption, orientation and position. Use Excel VBA to automate the list creation, then add your own comments in the description field, to help the users.
Create List of Pivot Table Fields
The following code adds a new sheet, named "Pivot_Fields_List", to the workbook. Then it creates a list of all the pivot fields in the first pivot table on the active sheet.
The list includes the pivot field's caption, source name, location (orientation), position at that location, sample data from the field, and a formula (calculated fields)
Because of a problem with the Orientation method, all the data fields are listed as "Hidden".
Sub ListPivotFields() Dim lRow As Long Dim wsList As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim strList As String Dim strLoc As String On Error GoTo errHandler Set pt = ActiveSheet.PivotTables(1) strList = "Pivot_Fields_List" Application.DisplayAlerts = False On Error Resume Next Sheets(strList).Delete On Error GoTo errHandler Set wsList = Sheets.Add lRow = 2 With wsList .Name = strList .Cells(1, 1).Value = "Caption" .Cells(1, 2).Value = "Source Name" .Cells(1, 3).Value = "Location" .Cells(1, 4).Value = "Position" .Cells(1, 5).Value = "Sample Item" .Cells(1, 6).Value = "Formula" .Cells(1, 7).Value = "Description" .Rows(1).Font.Bold = True For Each pf In pt.PivotFields If pf.Caption <> "Values" Then .Cells(lRow, 1).Value = pf.Caption .Cells(lRow, 2).Value = pf.SourceName Select Case pf.Orientation Case xlHidden strLoc = "Hidden" Case xlRowField strLoc = "Row" Case xlColumnField strLoc = "Column" Case xlPageField strLoc = "Page" Case xlDataField strLoc = "Data" End Select .Cells(lRow, 3).Value = strLoc .Cells(lRow, 4).Value = pf.Position On Error Resume Next If pf.PivotItems.Count > 0 Then .Cells(lRow, 5).Value = pf.PivotItems(1).Value End If On Error GoTo errHandler 'print the formula for calculated fields If pf.IsCalculated = True Then .Cells(lRow, 6).Value = _ Right(pf.Formula, Len(pf.Formula) - 1) End If lRow = lRow + 1 End If Next pf End With exitHandler: Application.DisplayAlerts = True Exit Sub errHandler: MsgBox "Could not create list" Resume exitHandler End Sub
Create List of Pivot Fields By Location
The following code adds a new sheet, named "Pivot_FieldLoc_List", to the workbook. Then it creates a list of all the pivot fields in the first pivot table on the active sheet.
The list is organized by location type, and includes the pivot field's caption, source name, location (orientation), position at that location, sample data from the field, and a formula (calculated fields)
The data fields are listed as "Data", but no sample data or formulas are shown for the data fields.
Sub PivotFieldListByPosition() Dim lRow As Long Dim wsList As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim strList As String On Error GoTo errHandler Set pt = ActiveSheet.PivotTables(1) strList = "Pivot_FieldLoc_List" Application.DisplayAlerts = False On Error Resume Next Sheets(strList).Delete On Error GoTo errHandler Set wsList = Sheets.Add lRow = 2 With wsList .Name = strList .Cells(1, 1).Value = "Caption" .Cells(1, 2).Value = "Source Name" .Cells(1, 3).Value = "Location" .Cells(1, 4).Value = "Position" .Cells(1, 5).Value = "Sample Item" .Cells(1, 6).Value = "Formula" .Cells(1, 7).Value = "Description" .Rows(1).Font.Bold = True For Each pf In pt.PageFields .Cells(lRow, 1).Value = pf.Caption .Cells(lRow, 2).Value = pf.SourceName .Cells(lRow, 3).Value = pf.Orientation & " - Page" .Cells(lRow, 4).Value = pf.Position .Cells(lRow, 5).Value = pf.PivotItems(1).Value lRow = lRow + 1 Next pf For Each pf In pt.RowFields If pf.Caption <> "Values" Then .Cells(lRow, 1).Value = pf.Caption .Cells(lRow, 2).Value = pf.SourceName .Cells(lRow, 3).Value = pf.Orientation & " - Row" .Cells(lRow, 4).Value = pf.Position .Cells(lRow, 5).Value = pf.PivotItems(1).Value lRow = lRow + 1 End If Next pf For Each pf In pt.ColumnFields If pf.Caption <> "Values" Then .Cells(lRow, 1).Value = pf.Caption .Cells(lRow, 2).Value = pf.SourceName .Cells(lRow, 3).Value = pf.Orientation & " - Column" .Cells(lRow, 4).Value = pf.Position .Cells(lRow, 5).Value = pf.PivotItems(1).Value lRow = lRow + 1 End If Next pf For Each pf In pt.DataFields .Cells(lRow, 1).Value = pf.Caption .Cells(lRow, 2).Value = pf.SourceName .Cells(lRow, 3).Value = pf.Orientation & " - Data" .Cells(lRow, 4).Value = pf.Position lRow = lRow + 1 Next pf For Each pf In pt.HiddenFields If pf.Caption <> "Values" Then .Cells(lRow, 1).Value = pf.Caption .Cells(lRow, 2).Value = pf.SourceName .Cells(lRow, 3).Value = pf.Orientation & " - Hidden" .Cells(lRow, 4).Value = pf.Position On Error Resume Next 'sample item in field, where applicable .Cells(lRow, 5).Value = pf.PivotItems(1).Value 'print the formula for calculated fields .Cells(lRow, 6).Value = " " & pf.Formula On Error GoTo errHandler lRow = lRow + 1 End If Next pf End With exitHandler: Application.DisplayAlerts = True Exit Sub errHandler: MsgBox "Could not create list" Resume exitHandler End Sub
Contextures Inc., Copyright ©2010
All rights reserved.
Last updated: September 7, 2010
