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.
Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples. This is the best way to learn. You’ll be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers.
Click below to subscribe and gain access to the subscriber area. You will also receive:
- My favorite tips and tricks direct to your inbox
- Exclusive content (which is only available to subscribers)
- FREE tools and downloads
If you’re already a subscriber, click here to log-in to the subscriber downloads area.
The filename for this post is 0047 Excel VBA for Pivot Tables.zip.
Contents
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.
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
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 End 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
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: