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.
|
1 2 3 4 5 |
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:
|
1 2 |
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 |
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:
|
1 |
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.
|
1 2 3 |
Application.ScreenUpdating = False ' Your code to work with the pivot table Application.ScreenUpdating = True |
Full Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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.