Excel VBA to Get Pivot Table Field Names (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I am going to show how to use Excel VBA to get pivot table field names. In Excel, when working with Pivot Tables, accessing and managing field names can sometimes pose a challenge. Fortunately, we can utilize the power of VBA to make a list of Pivot Table field names to make data handling easier and more efficient. So, let’s explore how we can harness the power of VBA to efficiently retrieve Pivot Table field names.


Excel VBA to Get Pivot Table Field Names: 3 Useful Methods

In this section, we will demonstrate 3 examples of creating a list of pivot table field names using Excel VBA. Let’s explore the methods one by one. But before that, let’s check our dataset first. Here, I have two pivot tables in separate sheets named Sheet1 & Sheet2. The picture below is the first pivot table containing the Sum of Revenue of a Sales dataset.

Pivot Table 1 in Sheet1

The 2nd pivot table contains Ticket Selling Information in Sheet2.

Pivot Table 2 in Sheet2

So our target is to create a list of all the pivot fields that the two pivot tables contain.


1. Create List of Pivot Table Fields in Pivot Table Order of Active Sheet

Here, we will see a VBA code that can generate a list of all the Pivot Fields of the first pivot table in the active worksheet.

Code for generating List Of Pivot Fields of 1st Pivot Table in Active Sheet

VBA Code for generating List Of Pivot Fields of 1st Pivot Table in Active Sheet

VBA Code Syntax:

Option Explicit
Sub PivotFields_Name_TableOrder()
Dim lowest_Row As Long
Dim ws_List As Worksheet
Dim Pv_tbl As PivotTable
Dim Pv_fld As PivotField
Dim dt_fld As PivotField
Dim pi As PivotItem
Dim str_List As String
Dim str_Loc As String
str_List = "Pv_Fields_List1"
Application.DisplayAlerts = False
On Error Resume Next
Set Pv_tbl = ActiveSheet.PivotTables(1)
If Pv_tbl Is Nothing Then
  MsgBox "No pivot table found on active sheet"
  GoTo exit_Handler
End If
Sheets(str_List).Delete
On Error GoTo err_Handler
Set ws_List = Sheets.Add
lowest_Row = 2
With ws_List
  .Name = str_List
  .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 = "Notes"
  .Rows(1).Font.Bold = True
  For Each Pv_fld In Pv_tbl.PivotFields
    If Pv_fld.Caption <> "Values" Then
        .Cells(lowest_Row, 1).Value = Pv_fld.Caption
        .Cells(lowest_Row, 2).Value = Pv_fld.SourceName
        Select Case Pv_fld.Orientation
          Case xlHidden
            str_Loc = "Hidden"
          Case xlRowField
            str_Loc = "Row"
          Case xlColumnField
            str_Loc = "Column"
          Case xlPageField
            str_Loc = "Page"
          Case xlDataField
            str_Loc = "Data"
        End Select
        If str_Loc = "Hidden" Then
          For Each dt_fld In Pv_tbl.DataFields
            If dt_fld.SourceName _
                = Pv_fld.SourceName Then
              str_Loc = "Data"
              Exit For
            End If
          Next dt_fld
        End If
        .Cells(lowest_Row, 3).Value = str_Loc
        .Cells(lowest_Row, 4).Value = Pv_fld.Position
        On Error Resume Next
        If Pv_fld.PivotItems.Count > 0 Then
          .Cells(lowest_Row, 5).Value _
              = Pv_fld.PivotItems(1).Value
        End If
        On Error GoTo err_Handler
        'printing the formula for calculated fields
        If Pv_fld.IsCalculated = True Then
          .Cells(lowest_Row, 6).Value = _
              Right(Pv_fld.Formula, Len(Pv_fld.Formula) - 1)
        End If
        lowest_Row = lowest_Row + 1
      End If
  Next Pv_fld
  .Columns("A:G").EntireColumn.AutoFit
End With
exit_Handler:
  Application.DisplayAlerts = True
  Exit Sub
err_Handler:
  MsgBox "Failed to create list"
  Resume exit_Handler
End Sub
How Does the Code Work?
Dim lowest_Row As Long
Dim ws_List As Worksheet
Dim Pv_tbl As PivotTable
Dim Pv_fld As PivotField
Dim dt_fld As PivotField
Dim pi As PivotItem
Dim str_List As String
Dim str_Loc As String

How Does the Code Work?

  • Here we declared different types of variables
str_List = "Pv_Fields_List1"
  • Here, we assigned a string to the variable str_List which will be the name of the new worksheet where our pivot field list will be created.
Set Pv_tbl = ActiveSheet.PivotTables(1)
  • Here, we set the first pivot table of the active worksheet to the PivotTable type variable named Pv_tbl .
If Pv_tbl Is Nothing Then
  MsgBox "No pivot table found on active sheet"
  GoTo exit_Handler
End If
  • If no Pivot Table is found on the active sheet then a MsgBox will show up informing that to the user. Next, the code will jump into the exit_Handler section.
Sheets(str_List).Delete
On Error GoTo err_Handler
  • If a worksheet with the same name as Pv_Fields_List1 already exists, then the code will first delete the sheet. If any error occurs, it will jump to err_Handler section.
Set ws_List = Sheets.Add
  • This code will add a new worksheet named Pv_Fields_List1 and assign the worksheet to the variable named ws_List.
With ws_List
  .Name = str_List
  .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 = "Notes"
  .Rows(1).Font.Bold = True
  • Here, the code writes down the labels on the header columns of the Pivot Field list. In the list, we will extract Caption, Source Name, Location, Position, Sample Item, Formula, and Notes.
For Each Pv_fld In Pv_tbl.PivotFields
    If Pv_fld.Caption <> "Values" Then
        .Cells(lowest_Row, 1).Value = Pv_fld.Caption
        .Cells(lowest_Row, 2).Value = Pv_fld.SourceName
  • Here, a For Each loop is executed to loop through each field of the 1st pivot table of the active worksheet. Then inside each loop, we first investigate if the field’s caption is “Values” or not. If it is not, then we write down the Caption and SourceName in the corresponding columns.
Select Case Pv_fld.Orientation
          Case xlHidden
            str_Loc = "Hidden"
          Case xlRowField
            str_Loc = "Row"
          Case xlColumnField
            str_Loc = "Column"
          Case xlPageField
            str_Loc = "Page"
          Case xlDataField
            str_Loc = "Data"
        End Select
  • Then we use Case syntax to handle multiple types of orientation of the pivot field. A pivot field can have xlHidden, xlRowField, xlColumnField, xlPageField, and xlDataField orientation. Hence, for each orientation, we set str_Loc to its corresponding value.
If str_Loc = "Hidden" Then
          For Each dt_fld In Pv_tbl.DataFields
            If dt_fld.SourceName _
                = Pv_fld.SourceName Then
              str_Loc = "Data"
              Exit For
            End If
          Next dt_fld
        End If
  • Here, for the orientation “Hidden” we set the orientation Data
Cells(lowest_Row, 3).Value = str_Loc
        .Cells(lowest_Row, 4).Value = Pv_fld.Position
        On Error Resume Next
  • Here, the location and field position is written on the list. Any error here will lead to the next line of code.
If Pv_fld.PivotItems.Count > 0 Then
          .Cells(lowest_Row, 5).Value _
              = Pv_fld.PivotItems(1).Value
        End If
        On Error GoTo err_Handler
  • If a pivot field contains any item, then we write down the first item in the Sample Item column.
If Pv_fld.IsCalculated = True Then
          .Cells(lowest_Row, 6).Value = _
              Right(Pv_fld.Formula, Len(Pv_fld.Formula) - 1)
        End If
        lowest_Row = lowest_Row + 1
  • If there is any formula in the pivot field, then we write down the formula in the Formula column.
End If
  Next Pv_fld
  .Columns("A:G").EntireColumn.AutoFit
End With
  • After entering the data for each column, the loop starts again for the next pivot field.
exit_Handler:
  Application.DisplayAlerts = True
  Exit Sub
  • Here, the prompt alert is turned on. Then the subroutine will exit.
err_Handler:
  MsgBox "Failed to create list"
  Resume exit_Handler
End Sub
  • Here, a MsgBox will open informing the failure to create the list of Pivot table fields.
  • Now, if we run the code while keeping the Sheet1 as the active sheet, we will see that a new worksheet will be created named “Pv_Fields_List1” and it will contain the list of field names of the pivot table in Sheet1.

List of Pivot Table Field Names of 1st Pivot Table in Sheet1

  • If we compare with the actual pivot table, we will see that there were a total of 8 fields, of which 2 were hidden. In the last column, “Notes“, we can manually write down anything important to us.
  • Now, if we make Sheet2 the active sheet and rerun the code, first, the existing Pv_Fields_List1 will be deleted, and then a new worksheet with the same name will be created that will contain the list of all the field names of the pivot table in Sheet2. Here is the list of pivot field names of the pivot table in Sheet2.

List of Pivot Table Field Names of 1st Pivot Table in Sheet2

  • Here, we can see that the order of the pivot field names is just as the default field list. If we want to order the names with respect to their location (Row, Column, Hidden) then we can use the second method.

2. Create List of Pivot Table Field Names in Location Order of Active Sheet

In this example, we will create a list of pivot table field names of the 1st pivot table in the active worksheet, just like in the first method. But the order of the field names will be according to their locations. The VBA code is given below.

VBA Code for generating List Of Pivot Fields of 1st Pivot Table in Active Sheet with Local Order

VBA Code for generating List Of Pivot Fields of 1st Pivot Table in Active Sheet with Local Order

VBA Code for generating List Of Pivot Fields of 1st Pivot Table in Active Sheet with Local Order

VBA Code Syntax:

Option Explicit
Sub PivotFields_Name_LocListOrder()
Dim lowest_Row  As Long
Dim ws_List As Worksheet
Dim Pv_tbl As PivotTable
Dim Pv_fld As PivotField
Dim dt_fld As PivotField
Dim pi As PivotItem
Dim str_List As String
Dim l_Loc As Long
str_List = "Pivot_FieldLoc_List"
Application.DisplayAlerts = False
On Error Resume Next
Set Pv_tbl = ActiveSheet.PivotTables(1)
If Pv_tbl Is Nothing Then
  MsgBox "No pivot table on active sheet"
  GoTo exit_Handler
End If
Sheets(str_List).Delete
On Error GoTo err_Handler
Set ws_List = Sheets.Add
lowest_Row = 2
With ws_List
  .Name = str_List
  .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 = "Notes"
  .Rows(1).Font.Bold = True
  For Each Pv_fld In Pv_tbl.PageFields
    l_Loc = Pv_fld.Orientation
    .Cells(lowest_Row, 1).Value = Pv_fld.Caption
    .Cells(lowest_Row, 2).Value = Pv_fld.SourceName
    .Cells(lowest_Row, 3).Value = l_Loc & " - Page"
    .Cells(lowest_Row, 4).Value = Pv_fld.Position
    On Error Resume Next
    If Pv_fld.PivotItems.Count > 0 Then
      .Cells(lowest_Row, 5).Value _
          = Pv_fld.PivotItems(1).Value
    End If
    On Error GoTo err_Handler
    lowest_Row = lowest_Row + 1
    l_Loc = 0
  Next Pv_fld
  For Each Pv_fld In Pv_tbl.RowFields
    l_Loc = Pv_fld.Orientation
    If Pv_fld.Caption <> "Values" Then
      .Cells(lowest_Row, 1).Value = Pv_fld.Caption
      .Cells(lowest_Row, 2).Value = Pv_fld.SourceName
      .Cells(lowest_Row, 3).Value = l_Loc & " - Row"
      .Cells(lowest_Row, 4).Value = Pv_fld.Position
      On Error Resume Next
      If Pv_fld.PivotItems.Count > 0 Then
        .Cells(lowest_Row, 5).Value _
            = Pv_fld.PivotItems(1).Value
      End If
      On Error GoTo err_Handler
      lowest_Row = lowest_Row + 1
      l_Loc = 0
    End If
  Next Pv_fld
  For Each Pv_fld In Pv_tbl.ColumnFields
    l_Loc = Pv_fld.Orientation
    If Pv_fld.Caption <> "Values" Then
      .Cells(lowest_Row, 1).Value = Pv_fld.Caption
      .Cells(lowest_Row, 2).Value = Pv_fld.SourceName
      .Cells(lowest_Row, 3).Value = l_Loc & " - Column"
      .Cells(lowest_Row, 4).Value = Pv_fld.Position
      On Error Resume Next
      If Pv_fld.PivotItems.Count > 0 Then
        .Cells(lowest_Row, 5).Value _
            = Pv_fld.PivotItems(1).Value
      End If
      On Error GoTo err_Handler
      lowest_Row = lowest_Row + 1
      l_Loc = 0
    End If
  Next Pv_fld
  For Each Pv_fld In Pv_tbl.DataFields
    l_Loc = Pv_fld.Orientation
    Set dt_fld = Pv_tbl.PivotFields(Pv_fld.SourceName)
    .Cells(lowest_Row, 1).Value = dt_fld.Caption
    .Cells(lowest_Row, 2).Value = dt_fld.SourceName
    .Cells(lowest_Row, 3).Value = l_Loc & " - Data"
    .Cells(lowest_Row, 4).Value = dt_fld.Position
    On Error Resume Next
        If dt_fld.IsCalculated = True Then
          .Cells(lowest_Row, 6).Value = _
              Right(dt_fld.Formula, Len(dt_fld.Formula) - 1)
        End If
    On Error GoTo err_Handler
    lowest_Row = lowest_Row + 1
    l_Loc = 0
    Set dt_fld = Nothing
  Next Pv_fld
  For Each Pv_fld In Pv_tbl.HiddenFields
    l_Loc = Pv_fld.Orientation
    If Pv_fld.Caption <> "Values" Then
      .Cells(lowest_Row, 1).Value = Pv_fld.Caption
      .Cells(lowest_Row, 2).Value = Pv_fld.SourceName
      .Cells(lowest_Row, 3).Value = l_Loc & " - Hidden"
      .Cells(lowest_Row, 4).Value = Pv_fld.Position
      On Error Resume Next
      'printing sample item in field if possible
      .Cells(lowest_Row, 5).Value = Pv_fld.PivotItems(1).Value
      'printing formula for calculated fields
      .Cells(lowest_Row, 6).Value = " " & Pv_fld.Formula
      On Error GoTo err_Handler
      lowest_Row = lowest_Row + 1
      l_Loc = 0
    End If
  Next Pv_fld
  .Columns("A:G").EntireColumn.AutoFit
End With
exit_Handler:
    Application.DisplayAlerts = True
    Exit Sub
err_Handler:
    MsgBox "Could not create list"
    Resume exit_Handler
End Sub

How Does the Code Work?

The code works in a similar way as the code in the first example. However, there are some differences regarding how we ordered the field names in the list. We will only discuss the differences below.

  • Here, we named the new worksheet as Pivot_FieldLoc_List
  • In the first code, all types of pivot fields were looped through at once. But in this code, we looped through each type of field (PageFields, RowFields, ColumnFields, DataFields and HiddenFields) individually. Moreover, we also wrote their location with the type of field in the Location.

Now, if we run the code while taking Sheet2 as an active sheet, we will have the following result.

List of Pivot Table Field Names of 1st Pivot Table in Sheet2 with Location Order


3. List of All Pivot Tables Field Names on All Sheets in Active Workbook

If you want to list out the field names of all the pivot tables across the workbook, you can use the following VBA code. It will list out all the field names in order of location.

VBA Code to Create List of Pivot Table Field Names of All the Pivot Tables on All Sheets in the Active Workbook

Code to Create List of Field Names of All the Tables on All Sheets in the Active Workbook

Code to Create List of Pivot Table Field Names of All the Pivot Tables on All Sheets in the Active Workbook

Code to Create List of Pivot Table Field Names of All the Pivot Tables on All Sheets in the Active Workbook

VBA Code :

Option Explicit
Sub ALL_PTs_PFs_LocList_Order()
Dim lowest_Row  As Long
Dim ws As Worksheet
Dim ws_List As Worksheet
Dim Pv_tbl As PivotTable
Dim Pv_fld As PivotField
Dim dt_fld As PivotField
Dim pi As PivotItem
Dim l_Loc As Long
Dim l_Pos As Long
Dim pf_Count As Long
Dim my_List As ListObject
Dim bOLAP As Boolean
Application.DisplayAlerts = False
On Error GoTo err_Handler
Set ws_List = Sheets.Add
lowest_Row = 2
With ws_List
  .Cells(1, 1).Value = "Sheet"
  .Cells(1, 2).Value = "PT Name"
  .Cells(1, 3).Value = "PT Address"
  .Cells(1, 4).Value = "Caption"
  .Cells(1, 5).Value = "Heading"
  .Cells(1, 6).Value = "Source Name"
  .Cells(1, 7).Value = "Location"
  .Cells(1, 8).Value = "Position"
  .Cells(1, 9).Value = "Sample Item"
  .Cells(1, 10).Value = "Formula"
  .Cells(1, 11).Value = "OLAP"
  .Rows(1).Font.Bold = True
  For Each ws In ActiveWorkbook.Worksheets
    For Each Pv_tbl In ws.PivotTables
      bOLAP = Pv_tbl.PivotCache.OLAP
      For pf_Count = 1 To Pv_tbl.RowFields.Count
        Set Pv_fld = Pv_tbl.RowFields(pf_Count)
        l_Loc = Pv_fld.Orientation
        If Pv_fld.Caption <> "Values" Then
        .Cells(lowest_Row, 1).Value = ws.Name
        .Cells(lowest_Row, 2).Value = Pv_tbl.Name
        .Cells(lowest_Row, 3).Value = Pv_tbl.TableRange2.Address
        .Cells(lowest_Row, 4).Value = Pv_fld.Caption
        .Cells(lowest_Row, 5).Value = Pv_fld.LabelRange.Address
        .Cells(lowest_Row, 6).Value = Pv_fld.SourceName
        .Cells(lowest_Row, 7).Value = l_Loc & " - Row"
        .Cells(lowest_Row, 8).Value = pf_Count
          On Error Resume Next
          If Pv_fld.PivotItems.Count > 0 _
            And bOLAP = False Then
            .Cells(lowest_Row, 9).Value _
                = Pv_fld.PivotItems(1).Value
          End If
          On Error GoTo err_Handler
        .Cells(lowest_Row, 11).Value = bOLAP
          lowest_Row = lowest_Row + 1
          l_Loc = 0
        End If
      Next pf_Count
      For pf_Count = 1 To Pv_tbl.ColumnFields.Count
        Set Pv_fld = Pv_tbl.ColumnFields(pf_Count)
        l_Loc = Pv_fld.Orientation
        If Pv_fld.Caption <> "Values" Then
        .Cells(lowest_Row, 1).Value = ws.Name
        .Cells(lowest_Row, 2).Value = Pv_tbl.Name
        .Cells(lowest_Row, 3).Value = Pv_tbl.TableRange2.Address
        .Cells(lowest_Row, 4).Value = Pv_fld.Caption
        .Cells(lowest_Row, 5).Value = Pv_fld.LabelRange.Address
        .Cells(lowest_Row, 6).Value = Pv_fld.SourceName
        .Cells(lowest_Row, 7).Value = l_Loc & " - Column"
        .Cells(lowest_Row, 8).Value = pf_Count
          On Error Resume Next
          If Pv_fld.PivotItems.Count > 0 _
            And bOLAP = False Then
            .Cells(lowest_Row, 9).Value _
                = Pv_fld.PivotItems(1).Value
          End If
          On Error GoTo err_Handler
        .Cells(lowest_Row, 11).Value = bOLAP
          lowest_Row = lowest_Row + 1
          l_Loc = 0
        End If
      Next pf_Count
      For pf_Count = 1 To Pv_tbl.PageFields.Count
        Set Pv_fld = Pv_tbl.PageFields(pf_Count)
        l_Loc = Pv_fld.Orientation
        .Cells(lowest_Row, 1).Value = ws.Name
        .Cells(lowest_Row, 2).Value = Pv_tbl.Name
        .Cells(lowest_Row, 3).Value = Pv_tbl.TableRange2.Address
        .Cells(lowest_Row, 4).Value = Pv_fld.Caption
        .Cells(lowest_Row, 5).Value = Pv_fld.LabelRange.Address
        .Cells(lowest_Row, 6).Value = Pv_fld.SourceName
        .Cells(lowest_Row, 7).Value = l_Loc & " - Filter"
        .Cells(lowest_Row, 8).Value = pf_Count
        On Error Resume Next
          If Pv_fld.PivotItems.Count > 0 _
            And bOLAP = False Then
          .Cells(lowest_Row, 9).Value _
              = Pv_fld.PivotItems(1).Value
        End If
        On Error GoTo err_Handler
        .Cells(lowest_Row, 11).Value = bOLAP
        lowest_Row = lowest_Row + 1
        l_Loc = 0
      Next pf_Count
      For pf_Count = 1 To Pv_tbl.DataFields.Count
        Set Pv_fld = Pv_tbl.DataFields(pf_Count)
        l_Loc = Pv_fld.Orientation
        Set dt_fld = Pv_tbl.PivotFields(Pv_fld.SourceName)
        .Cells(lowest_Row, 1).Value = ws.Name
        .Cells(lowest_Row, 2).Value = Pv_tbl.Name
        .Cells(lowest_Row, 3).Value = Pv_tbl.TableRange2.Address
        .Cells(lowest_Row, 4).Value = dt_fld.Caption
        .Cells(lowest_Row, 5).Value = _
              Pv_fld.LabelRange.Cells(1).Address
       .Cells(lowest_Row, 6).Value = dt_fld.SourceName
        .Cells(lowest_Row, 7).Value = l_Loc & " - Data"
        .Cells(lowest_Row, 8).Value = pf_Count
        On Error Resume Next
            If dt_fld.IsCalculated = True Then
              .Cells(lowest_Row, 10).Value = _
                  Right(dt_fld.Formula, Len(dt_fld.Formula) - 1)
            End If
        On Error GoTo err_Handler
        .Cells(lowest_Row, 11).Value = bOLAP
        lowest_Row = lowest_Row + 1
        l_Loc = 0
        Set dt_fld = Nothing
      Next pf_Count
    Next Pv_tbl
  Next ws
  .Columns("A:K").EntireColumn.AutoFit
  Set my_List = .ListObjects.Add(xlSrcRange, _
      Range("A1").CurrentRegion)
End With
MsgBox "Done"
exit_Handler:
    Application.DisplayAlerts = True
    Exit Sub
err_Handler:
    MsgBox "Could not create list"
    Resume exit_Handler
End Sub

📌How Does the Code Work?

Sub ALL_PTs_PFs_LocList_Order()
  • Here, we took a new subroutine named ALL_PTs_PFs_LocList_Order.
Dim lowest_Row  As Long
Dim ws As Worksheet
Dim ws_List As Worksheet
Dim Pv_tbl As PivotTable
Dim Pv_fld As PivotField
Dim dt_fld As PivotField
Dim pi As PivotItem
Dim l_Loc As Long
Dim l_Pos As Long
Dim pf_Count As Long
Dim my_List As ListObject
Dim bOLAP As Boolean
  • Here, we declared different types of variables.
DisplayAlerts = False
On Error GoTo err_Handler
  • This will turn off the display alert. If any error occurs during code execution it will go to err_Handler section.
Set ws_List = Sheets.Add
lowest_Row = 2
  • Here, we added a new sheet and assigned it to the worksheet variable ws_List. Then, we set lowest_Row to 2.
With ws_List
  .Cells(1, 1).Value = "Sheet"
  .Cells(1, 2).Value = "PT Name"
  .Cells(1, 3).Value = "PT Address"
  .Cells(1, 4).Value = "Caption"
  .Cells(1, 5).Value = "Heading"
  .Cells(1, 6).Value = "Source Name"
  .Cells(1, 7).Value = "Location"
  .Cells(1, 8).Value = "Position"
  .Cells(1, 9).Value = "Sample Item"
  .Cells(1, 10).Value = "Formula"
  .Cells(1, 11).Value = "OLAP"
  .Rows(1).Font.Bold = True
  • Then, we added the header column in the newly created worksheet. Here, in the K column, we have added the title OLAP. In this column, it will be checked whether a pivot table field is from online analytical processing or not. This column will host the boolean variable bOLAP which will yield either TRUE or FALSE value.
For Each ws In ActiveWorkbook.Worksheets
    For Each Pv_tbl In ws.PivotTables
      bOLAP = Pv_tbl.PivotCache.OLAP
  • We have two For Each loops here. We loop through each worksheet in the entire workbook in the first For Each loop, and each pivot table in every worksheet in the second For Each loop. In this loop, we first check whether the pivot table is from online analytical processing.
For pf_Count = 1 To Pv_tbl.RowFields.Count
  • Similar to the 2nd example, we will list out the pivot fields according to their location or orientation. Hence, we run For loop individually for each type of location type pivot field (RowFields, ColumnFields, PageFields and DataFields). Under this for loop, we will extract the data for filling up the 11 columns in the list. Here, we will only explain the part of the code for RowFields.
Set Pv_fld = Pv_tbl.RowFields(pf_Count)
  • Then, we set a pivot field of RowFields type to the variable Pv_fld.
l_Loc = Pv_fld.Orientation
  • Then, we determine the orientation of the Pv_fld.
If Pv_fld.Caption <> "Values" Then
        .Cells(lowest_Row, 1).Value = ws.Name
        .Cells(lowest_Row, 2).Value = Pv_tbl.Name
        .Cells(lowest_Row, 3).Value = Pv_tbl.TableRange2.Address
        .Cells(lowest_Row, 4).Value = Pv_fld.Caption
        .Cells(lowest_Row, 5).Value = Pv_fld.LabelRange.Address
        .Cells(lowest_Row, 6).Value = Pv_fld.SourceName
        .Cells(lowest_Row, 7).Value = l_Loc & " - Row"
        .Cells(lowest_Row, 8).Value = pf_Count
          On Error Resume Next
  • Then, we first ensure that the caption of the Pv_fld is not  “Values” using the IF statement. After that, we write down values of columns Sheet, PT Name, PT Address, Caption, Heading, Source Name, Location, and Position.
If Pv_fld.PivotItems.Count > 0 _
            And bOLAP = False Then
            .Cells(lowest_Row, 9).Value _
                = Pv_fld.PivotItems(1).Value
          End If
  • Then, we write down the sample data (the first data, Pv_fld.PivotItems(1).Value) if there is any data in that pivot data field.
On Error GoTo err_Handler
        .Cells(lowest_Row, 11).Value = bOLAP
          lowest_Row = lowest_Row + 1
          l_Loc = 0
        End If
      Next pf_Count
  • Lastly, we write down the value in the bOLAP column. Then we increase the top empty column lowest_Row number by 1.
Next Pv_tbl
  Next ws
 .Columns("A:K").EntireColumn.AutoFit
  Set my_List = .ListObjects.Add(xlSrcRange, _
      Range("A1").CurrentRegion)
End With
MsgBox "Done"
  • After looping through all the pivot tables in all the worksheets, we turn the list of pivot table field names into a table. After finishing the task, a MsgBox will show that the task has been completed.
exit_Handler:
    Application.DisplayAlerts = True
    Exit Sub
  • In the exit_Handler section, it will turn on DisplayAlerts again. Then the sub exits.
err_Handler:
    MsgBox "Could not create list"
    Resume exit_Handler
End Sub
  • In the err_Handler section, a MsgBox will show this message“Could not create list”. With this, the subroutine ends.
  • After running the code, we have the following result in a new worksheet.

List of All the Pivot Table Field Names accross All the Worksheets

  • In this way, we can list out all the pivot table field names across all the worksheets.

How to Get the Pivot Table Name in Excel VBA

If you want to get the names of the existing pivot tables in the active worksheet, you can use the following VBA code.

Code for Determining Names of the Pivot Tables in the Active Worksheet

VBA Code Syntax:

Sub PVTableNames()
Dim PV_Table As PivotTable
For Each PV_Table In ActiveSheet.PivotTables
MsgBox PV_Table.Name
Next
End Sub
  • In this code, we loop through each pivot table in the active worksheet and then display the names in a MsgBox.

Return Field Name of Selected Cell in Pivot Table

Sometimes, when you select a heading in a pivot table (i.e. the column label) you may want to know what the field name of that column is. By running the following VBA code, you will be able to know the name of the field of the selected cell in a Pivot Table.

Code to Return Field Name of Selected Cell in Pivot Table

VBA Code:

Sub PT_Column_Header_Info()
 With ActiveCell.PivotCell
   Select Case .PivotCellType
      Case xlPivotCellPivotItem
         MsgBox "Selected cell is a Pivot Item" & vbCr _
            & "Its Pivot Field Name= " & .PivotField.SourceName
      Case xlPivotCellDataField
         MsgBox "Selected cell is a Data Field Label" & vbCr _
            & "Its Source Name= " & .DataField.SourceName
      Case Else
         MsgBox "Selected cell is not a PivotItem " & _
            "nor a Data Field Label"
   End Select
 End With
End Sub
  • It will be better if you add a button to the worksheet and then assign the macro to the button. Then, if you select a column label then click on the button, a MsgBox will inform the field name of that column.

Running VBA Code for Field Name of Selected Cell in Pivot Table


Things to Remember

  • The first 2 examples will only list the pivot fields that are in the first pivot table of the active worksheet. Hence, even if the active worksheet contains multiple pivot tables, it will not list out pivot field names of other pivot tables except the first one.
  • In the provided first 2 examples, before running the codes, make sure that you already do not have any sheets named Pv_Fields_List1 and Pivot_FieldLoc_List because the codes will first delete those sheets. So if you have any sheets named after these two strings and they contain anything that you don’t want to delete, rename those sheets.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

This concludes the article on how to use Excel VBA to get pivot table field names. If you found this post useful, please share it with your friends. Please let us know if you have any other questions.


Related Articles


<< Go Back to Pivot Table Field List | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo