We have two pivot tables in separate sheets named Sheet1 and Sheet2. The picture below is the first pivot table containing the Sum of Revenue of a Sales dataset.
The second pivot table contains Ticket Selling Information in Sheet2.
We will create a list of all the pivot fields that the two pivot tables contain.
Method 1 – Create a List of Pivot Table Fields Starting with the Active Sheet
Here’s the VBA code that can generate a list of all the Pivot Fields of the first pivot table in the active worksheet.
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 StringHow 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.
- 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.
- 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.
Method 2 – Create a List of Pivot Table Field Names in Location Order of Active Sheet
We will create a list of pivot table field names of the first pivot table in the active worksheet, just like in the first method. The order of the field names will be according to their locations. Here’s the code.
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 SubHow 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.
- 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.
If we run the code while taking Sheet2 as an active sheet, we will have the following result.
Method 3 – List of All Pivot Tables Field Names on All Sheets in the Active Workbook
Use the following VBA code. It will list out all the field names in order of location.
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 SubHow 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.
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.
VBA Code Syntax:
Sub PVTableNames()
Dim PV_Table As PivotTable
For Each PV_Table In ActiveSheet.PivotTables
MsgBox PV_Table.Name
Next
End SubReturn the Field Name of the Selected Cell in a Pivot Table
Use the following code:
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. If you select a column label, then click on the button, a MsgBox will inform the field name of that column.
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 the Practice Workbook
Related Articles
- How to Summarize Data in Excel Using Pivot Table
- [Fixed!] Pivot Table Field Name Already Exists
- Pivot Table Field Name Is Not Valid
<< Go Back to Pivot Table Field List | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
