---
title: "Excel VBA for Pivot Tables - Excel Off The Grid"
url: https://stacklist.com/card/031c29b8-37d0-4685-82ef-16c4ed83d11c
source_url: "https://exceloffthegrid.com/pivot-tables/"
stack: https://stacklist.com/c/business/stack/c63a8f97-5e08-4630-8e71-f2794bad0e8b
summary: "Pivot Tables are essential Excel tools for data analysis that can be controlled and automated using VBA code. This tutorial provides code examples for refreshing, creating, deleting, and manipulating pivot tables programmatically."
tags: "pivot-tables, excel, vba, automation, data-analysis, tutorial"
key_entities: "Excel (technology), VBA (technology), Pivot Tables (technology), exceloffthegrid.com (organization), data-analysis (concept), automation (concept)"
classification: "tutorial"
content_hash: "sha256:f912b2ad553ff7e6941a381d5d416550dc6d6895cfd184556e3f69519b1b5f3b"
acp_version: "0.2"
token_counts_approximate: 2818
visibility: public
agent_accessible: true
status: "final"
---

# Excel VBA for Pivot Tables - Excel Off The Grid

Pivot Tables are a key tool for many Excel users to analyze data.  They are flexible and easy to use.  Combine the power of Pivot Tables with the automation of VBA, and we can analyze data even faster.  This post includes the essential code to control Pivot Tables with VBA. Table of Contents Refreshing Pivot Tables Refresh a Pivot Table Refresh all the Pivot Tables in the active workbook Refresh all the Pivot Tables on a worksheet Create a Pivot Table Deleting Pivot Tables Delete a single Pivot Table Delete all Pivot Tables in the workbook Change Pivot Table Source Turn off autofit column widths on all Pivot Tables Adding and removing columns, rows and values to a Pivot Table Add fields to a Pivot Table Add field to Pivot Table columns Add field to Pivot Table filters Position field in Pivot Table filters Remove field from Pivot Table values Position field in a Pivot Table Add field to Pivot Table values section Remove field from Pivot Table values section Clear Pivot Tables filters Apply filter to Pivot Table Apply multiple filters to a Pivot Table Add a calculated field Clear all fields from a Pivot Table Download the example file:  Join the free Insiders Program and gain access to the example file used for this post. File name: 0047 Excel VBA for PivotTables.zip Get access Refreshing Pivot Tables Of all the tasks we undertake on Pivot Tables, refreshing the data is the most common.  So, let’s start there. Refresh a Pivot Table The following code refreshes a single Pivot Table called PivotTable1 .  Change the value of the pvtName variable to be the name of your Pivot Table. Sub RefreshAPivotTable() 'Create a variable to hold name of Pivot Table Dim pvtName As String 'Assign Pivot Table name to variable pvtName = "PivotTable1" 'Refresh the Pivot Table ActiveSheet.PivotTables(pvtName).PivotCache.Refresh End Sub Refresh all the Pivot Tables in the active workbook The next code snippet refreshes all Pivot Tables in the active workbook. Sub RefreshAllPivotTables() 'Refresh all Pivot Tables ActiveWorkbook.RefreshAll End Sub Refresh all the Pivot Tables on a worksheet We can refresh all the Pivot Tables in a workbook with a single line of code.  However, to refresh all the Pivot Tables on a worksheet, we need to loop through and refresh each one. Sub RefreshAllPivotTablesWorkbook() 'Create a variable to hold worksheets Dim ws As Worksheet 'Create a variable to hold Pivot Tables Dim pvt As PivotTable 'Loop through each worksheet For Each ws In ActiveWorkbook.Worksheets 'Loop through each Pivot Table For Each pvt In ws.PivotTables 'refresh the Pivot Table pvt.RefreshTable Next pvt Next ws End Sub Create a Pivot Table The following VBA code creates a Pivot Table.  It requires 4 inputs: The sheet containing the source data The range containing the source data The sheet on which to place the Pivot Table The cell reference where to place the Pivot Table Sub CreatePivotTable() 'Create all the variables Dim pvtCache As PivotCache Dim pvt As PivotTable Dim pvtLocationWs As String Dim pvtLocationRng As String Dim pvtSourceWs As String Dim pvtSourceRng As String Dim pvtSourceFull As String Dim pvtLocationFull As String 'Set the sheet and ranges for the variables pvtSourceWs = "Sheet1" pvtSourceRng = "A1:D50" pvtLocationWs = "Sheet2" pvtLocationRng = "A1" 'Create the string for the source pvtSourceFull = pvtSourceWs & "!" & _ Range(pvtSourceRng).Address(ReferenceStyle:=xlR1C1) 'Create the string for the location pvtLocationFull = pvtLocationWs & "!" & _ Range(pvtLocationRng).Address(ReferenceStyle:=xlR1C1) 'Create the Pivot Cache Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=pvtSourceFull) 'Create the Pivot Table Set pvt = pvtCache.CreatePivotTable(TableDestination:=pvtLocationFull, _ TableName:="PivotTable1") End Sub The example above uses standard Excel ranges.  To use an Excel Table as the source, we can use the Table’s name without referencing the sheet. Deleting Pivot Tables This section contains two options for deleting Pivot Tables. Delete a single Pivot Table The following macro deletes a single Pivot Table.  Change the name of the pvtName variable to the name of the Pivot Table you wish to delete. Sub DeletePivotTable() 'Create a variable to hold name of Pivot Table Dim pvtName As String 'Assign Pivot Table name to variable pvtName = "PivotTable1" 'Delete the Pivot Table named PivotTable1 ActiveSheet.PivotTables(pvtName).TableRange2.Clear End Sub Delete all Pivot Tables in the workbook While we can refresh all Pivot Tables in one go, to delete Pivot Tables, we must loop through and delete each. Sub DeleteAllPivotTable() 'Create a variable to hold worksheets Dim ws As Worksheet 'Create a variable to hold Pivot Tables Dim pvt As PivotTable 'Loop through each sheet in the activeworkbook For Each ws In ActiveWorkbook.Worksheets 'Loop through each Pivot Table in the worksheet For Each pvt In ws.PivotTables 'Turn off auto fit pvt.TableRange2.Clear Next pvt Next ws End Sub Change Pivot Table Source This macro changes the source data for a Pivot Table. Sub ChangePivotTableSource() 'Create all the variables Dim pvtCache As PivotCache Dim pvt As PivotTable Dim pvtName As String Dim pvtSourceWs As String Dim pvtSourceRng As String Dim pvtSourceFull As String 'Set the sheet and ranges for the variables pvtName = "PivotTable1" pvtSourceWs = "Sheet1" pvtSourceRng = "H1:K4" Set pvt = ActiveSheet.PivotTables(pvtName) 'Create the string for the source pvtSourceFull = pvtSourceWs & "!" & _ Range(pvtSourceRng).Address(ReferenceStyle:=xlR1C1) 'Create a new Pivot Cache Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=pvtSourceFull) 'Change Pivot Cache the Pivot Table is pointed to pvt.ChangePivotCache pvtCache End Sub The example above uses standard Excel ranges.  To use an Excel Table as the source we can use the Table’s name without referencing the sheet. Turn off autofit column widths on all Pivot Tables The following macro changes the settings to retain column widths when a Pivot Table is refreshed. Sub TurnOffAutoFitColumnsPivotTables() 'Create a variable to hold worksheets Dim ws As Worksheet 'Create a variable to hold Pivot Tables Dim pvt As PivotTable 'Loop through each sheet in the activeworkbook For Each ws In ActiveWorkbook.Worksheets 'Loop through each Pivot Table in the worksheet For Each pt In ws.PivotTables 'Turn off auto fit pvt.HasAutoFormat = False 'Turn auto fit back on 'pvt.HasAutoFormat = True Next pvt Next ws End Sub Adding and removing columns, rows and values to a Pivot Table After creating a Pivot Table, we then need to add/remove fields and apply filters to get the view that we want. Add fields to a Pivot Table Sub AddFieldToPivotTableRows() Dim pvt As PivotTable Dim pvtFieldName As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Customer Name" pvt.PivotFields(pvtFieldName).Orientation = xlRowField End Sub Add field to Pivot Table columns Sub AddFieldToPivotTableColumns() Dim pvt As PivotTable Dim pvtFieldName As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Customer Name" pvt.PivotFields(pvtFieldName).Orientation = xlColumnField End Sub Add field to Pivot Table filters Sub AddFieldToPivotTableFilters() Dim pvt As PivotTable Dim pvtFieldName As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Customer Name" pvt.PivotFields(pvtFieldName).Orientation = xlPageField End Sub Position field in Pivot Table filters Sub PostionFieldInPivotTableFilters() Dim pvt As PivotTable Dim pvtFieldName As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Customer Name" pvt.PivotFields(pvtFieldName).Position = 1 End Sub Remove field from Pivot Table values Sub RemoveFieldFromPivotTableValues() Dim pvt As PivotTable Dim pvtFieldName As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Customer Name" pvt.PivotFields(pvtFieldName).Orientation = xlHidden End Sub Position field in a Pivot Table Sub PostionFieldInPivotTable() Dim pvt As PivotTable Dim pvtFieldName As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Customer Name" pvt.PivotFields(pvtFieldName).Position = 1 End Sub Add field to Pivot Table values section Sub AddFieldToPivotTableValues() Dim pvt As PivotTable Dim pvtFieldName As String Dim pvtFieldDescription As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldName = "Revenue" pvtFieldDescription = "Sum of Revenue" pvt.AddDataField pvt.PivotFields(pvtFieldName), pvtFieldDescription, xlSum End Sub Remove field from Pivot Table values section Sub RemoveValueFieldFromPivotTableValues() Dim pvt As PivotTable Dim pvtFieldDescription As String Set pvt = ActiveSheet.PivotTables("PivotTable1") pvtFieldDescription = "Sum of Revenue" pvt.PivotFields(pvtFieldDescription).Orientation = xlHidden End Sub Clear Pivot Tables filters Sub ClearFiltersToPivotTable() Dim pvt As PivotTable Dim pvtField As PivotField Dim pvtFieldName As String Dim pvtName As String pvtName = "PivotTable1" Set pvt = ActiveSheet.PivotTables(pvtName) pvtFieldName = "Customer Name" Set pvtField = pvt.PivotFields(pvtFieldName) pvtField.ClearAllFilters End Sub Apply filter to Pivot Table Sub ApplyFilterToPivotTable() Dim pvt As PivotTable Dim pvtField As PivotField Dim pvtFieldName As String Dim pvtName As String Dim pvtFilter As String pvtName = "PivotTable1" Set pvt = ActiveSheet.PivotTables(pvtName) pvtFieldName = "Customer Name" Set pvtField = pvt.PivotFields(pvtFieldName) pvtFilter = "A" pvtField.ClearAllFilters pvtField.CurrentPage = pvtFilter E nd Sub Apply multiple filters to a Pivot Table Sub ApplyMultipleFiltersToPivotTable() Dim pvt As PivotTable Dim pvtField As PivotField Dim pvtItems As PivotItems Dim pvtFieldName As String Dim pvtName As String Dim pvtFilters As Variant Dim i As Integer Dim j As Integer pvtName = "PivotTable1" Set pvt = ActiveSheet.PivotTables(pvtName) pvtFieldName = "Customer Name" Set pvtField = pvt.PivotFields(pvtFieldName) pvtFilters = "A, B" pvtFilters = Split(pvtFilters, ", ") pvtField.ClearAllFilters pvtField.EnableMultiplePageItems = True For i = 1 To pvtField.PivotItems.Count For j = LBound (pvtFilters) To UBound (pvtFilters) If pvtField.PivotItems(i) = pvtFilters(j) Then pvtField.PivotItems(i).Visible = True Exit For Else pvtField.PivotItems(i).Visible = False End If Next j Next i End Sub Add a calculated field Sub AddCalculatedField() Dim pvtName As String Dim pvt As PivotTable Dim pvtCalcName As String Dim pvtCalc As String pvtName = "PivotTable1" Set pvt = ActiveSheet.PivotTables(pvtName) pvtCalcName = "myCalculation1" pvtCalc = "=Field1+Field2" pvt.CalculatedFields.Add pvtCalcName, pvtCalc, True End Sub Clear all fields from a Pivot Table Sub ClearAllFieldsFromPivotTable() Dim pvtName As String Dim pvt As PivotTable pvtName = "PivotTable1" Set pvt = ActiveSheet.PivotTables(pvtName) pvt.ClearTable End Sub What next? Discover how you can automate your work with our Excel courses and tools. The Excel Academy Make working late a thing of the past. The Excel Academy is Excel training for professionals who want to save time. GET ACCESS ➜ LEARN MORE ➜