How to Reference a Pivot Table in VBA

Pivot tables are powerful tools in Excel that summarize data, enabling quick analysis and reporting. Automating the interaction with pivot tables using VBA can greatly enhance your productivity. This tutorial will guide you through the process of referencing and manipulating a pivot table in VBA, ensuring you can automate your Excel tasks effectively.

Step 1: Understand the Workbook and Worksheet Object

Before you can work with pivot tables in VBA, it’s essential to understand how to reference the workbook and worksheet containing your pivot table. You will typically use the Workbook and Worksheet objects to accomplish this.

Dim wb As Workbook
Set wb = ThisWorkbook 'or Workbooks("YourWorkbookName.xlsx")

Dim ws As Worksheet
Set ws = wb.Worksheets("YourSheetName")

Step 2: Reference a Pivot Table by Name

To reference a pivot table, you first need to know its name. You can find the pivot table’s name in the PivotTable Fields pane in Excel. Once you have the name, you can reference the pivot table with the following code:

Dim pt As PivotTable
Set pt = ws.PivotTables("YourPivotTableName")

Ensure that “YourPivotTableName” matches the name of the pivot table you want to work with.

Step 3: Referencing Pivot Fields, Items, and Data

With the pivot table referenced, you can interact with its various elements, such as fields and items. For example, you may want to reference a specific field or change a field’s position in the layout:

Dim pf As PivotField
Set pf = pt.PivotFields("YourFieldName")
' Now you can work with this field, e.g. change its orientation or position
pf.Orientation = xlRowField
pf.Position = 1

Similarly, to work with individual items within a field:

Dim pi As PivotItem
Set pi = pf.PivotItems("YourItemName")
' Now you can work with the pivot item, for example, hide or show it
pi.Visible = True or False

Step 4: Accessing Pivot Table Data

To reference a specific cell within a pivot table, which could be valuable for dynamic reporting or dashboards, you use the PivotTable.GetPivotData method:

Dim dataField As PivotField
Set dataField = pt.DataFields("YourDataFieldName")

Dim cellValue As Range
Set cellValue = pt.GetPivotData(DataField:=dataField.Name, _
                                PivotItem1:=pf.Name, _
                                Item1:="YourItemName")
MsgBox cellValue.Value

This will display the value from the specified coordinates within the pivot table.

Step 5: Updating the Pivot Table

After changing the pivot table, such as filtering a field or adding a new data field, you will often need to refresh the pivot table to update its display with the new configuration:

pt.RefreshTable

This code refreshes the data of the specified pivot table so that it matches any new or modified source data.

Note: It’s a best practice to turn off screen updating when you manipulate pivot tables using VBA to speed up your code.

Application.ScreenUpdating = False
' Your code to work with the pivot table
Application.ScreenUpdating = True

Full Code:

Sub ReferencePivotTable()
    Dim wb As Workbook
    Set wb = ThisWorkbook

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Worksheets("Sheet2")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "Sheet 'Sheet5' not found!", vbExclamation
        Exit Sub
    End If

    Dim pt As PivotTable
    On Error Resume Next
    Set pt = ws.PivotTables("PivotTable1")
    On Error GoTo 0

    If pt Is Nothing Then
        MsgBox "PivotTable 'PivotTable1' not found on 'Sheet3'!", vbExclamation
        Exit Sub
    End If

    Dim pf As PivotField
    On Error Resume Next
    ' Use the index of the PivotField instead of the name
    Set pf = pt.PivotFields("Department") ' Adjust the index as needed
    On Error GoTo 0

    If pf Is Nothing Then
        MsgBox "PivotField 'Department' not found in 'PivotTable1'!", vbExclamation
        Exit Sub
    End If

    ' Set the "Department" field to be a row field at position 1
    pf.Orientation = xlRowField
    pf.Position = 1

    ' Refresh the pivot table
    pt.RefreshTable

    ' Enable screen updating (in case it was turned off)
    Application.ScreenUpdating = True
End Sub

Result

Pivot table:

Table Fields:

Conclusion

Referencing a pivot table in VBA is an essential skill for automating your Excel workflows. By following the steps outlined in this tutorial, you can easily interact with and manipulate pivot tables, making your reports and analyses more dynamic and responsive. Remember to reference the pivot table and its components accurately, and refresh the table after making changes to see up-to-date results.