Contextures

Excel Macro Lists All Pivot Table Fields

Use this macro to create a list of the fields in a pivot table, and details for each field, such as caption and position. Also see Macro to List All Pivot Fields and Pivot Items

Introduction

Create a list of the fields in a pivot table, along with information about each field, such as the caption, orientation and position. Use an Excel macro to make the list, then add your own comments in the description field, to help the users.

list of pivot table fields

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". go to top

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. go to top

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

More Pivot Table Tutorials

How to Plan and Set Up a Pivot Table

Excel Slicer Macros

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

go to top

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: May 20, 2017 9:41 AM
Contextures RSS Feed