Excel VBA Pivot Table Field Info List

Search Contextures Sites

Excel VBA Pivot Table Field Info List
Amazon.com

Amazon.com

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
Pivot Table Tutorials

Pivot Table Introduction
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007
Custom Calculations
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data
Filters, Top 10
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache
PivotTable Style
Printing a Pivot Table
Protection
Running Totals
Show and Hide Items
Sorting
Subtotals
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007)
Pivot Tables Recipe Book (Excel 2003)
Pivot Tables Recipe Book (Excel 2007)

Pivot Table Add-Ins

Pivot Power
Pivot Play PLUS

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright �2012
All rights reserved.

Debra Dalgleish

Last updated: September 7, 2010

Amazon.com Amazon.com	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