Excel VBA for Pivot Tables

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 example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download the file: 0047 Excel VBA for Pivot Tables.zip

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

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