VBA PivotTable

Many corporate reports boil down to the pivot tables that need to be created and updated.

VBA helps automate this process.

Creating an Excel PivotTable

We will use as base the table present in the worksheet of the following image:

Note that the name of the columns is present in row 1 and the data is complete until cell "D9".

First we determine the data on which we will work:

Dim TabRange As Range

Set TabRange = Cells(1, 1).CurrentRegion 'Associate range with data to variable


And we associate this data with an accessible source for the PivotTable. For this we will use a specific cache variable (fast access memory) proper for these cases.

Dim TabCache As PivotCache 'Data type for PivotTable

'Sets the PivotTable data source (which will be cached)
Set TabCache = ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:=TabRange)


The use of _ (underline) does not interfere with execution, but rather in code visualization, as it allows for line breaks when using consecutive methods and properties.

'Both the lines below starting from Set are equal and perform the same action
Set TabCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=TabRange)
Set TabCache = ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:=TabRange)


Then we determine where the PivotTable will stay. In our case, we will build on a dedicated tab.

ActiveWorkbook.Sheets.Add 'Adds a new spreadsheet, which becomes active
ActiveSheet.Name = "PivotTable"


We insert the PivotTable itself:

Dim TabDin As PivotTable 'Type for PivotTable

'Insert the PivotTable into the worksheet
Set TabDin = TabCache.CreatePivotTable _
(TableDestination:=Cells(1, 1), TableName:="PivotTable1")


The PivotTable is now ready for use. To manipulate your fields we must call them with PivotFields, determining its orientation (row, column or aggregate values) with property .Orientation.

    'Adds item to line titles
TabDin.PivotFields("Year").Orientation = xlRowField

'Adds the item to column headings
TabDin.PivotFields("Region").Orientation = xlColumnField

'Enters aggregate data of Amounts by sum
With TabDin.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
End With


With is used to associate or call different methods and properties of an object at the same time (required for some operations).

No With

  Cells(1,1).Value=-10
Cells(1,1).Interior.Color = RGB(255, 0, 0)


With

  With Cells(1,1)
.Value=-10
.Interior.Color = RGB(255, 0, 0)
End With


Putting together all the steps we would have:

Sub creatPivotTable()

Dim TabRange As Range
Dim TabCache As PivotCache 'Data Type for PivotTable
Dim TabDin As PivotTable 'Type for PivotTable

Set TabRange = Cells(1, 1).CurrentRegion 'Associate range with data to variable

'Defines the data source of the PivotTable (which will be cached)
Set TabCache = ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:=TabRange)

ActiveWorkbook.Sheets.Add 'Adds a new spreadsheet, which becomes active
ActiveSheet.Name = "PivotTable"

'Insert PivotTable in Worksheet
Set TabDin = TabCache.CreatePivotTable _
(TableDestination:=Cells(1, 1), TableName:="PivotTable1")

'Adds item to line titles
TabDin.PivotFields("Year").Orientation = xlRowField

'Add item to column headings
TabDin.PivotFields("Region").Orientation = xlColumnField

'Enters aggregate data of Amounts by sum
With TabDin.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
End With

End Sub


Update PivotTable

To update a PivotTable where only values of the data source has been modified (and not interval size changes) you can use the .Refresh.

Sub RefreshValues()

'Update only a PivotTable
Worksheets("PivotTable").PivotTables("PivotTable").PivotCache.Refresh

'Refresh all PivotTables in the document
ActiveWorkbook.RefreshAll

End Sub


There must be a PivotTable so that the above code does not result in an error.

If new lines are added to the data source of the PivotTable, we can update it by loading a new cache to it with the .ChangePivotCache method.

Sub UpdatePivotTable()

Set UpdatedRange = Worksheets("Sheet1").Cells(1, 1).CurrentRegion

Set UpdatedCache = ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:=UpdatedRange)

Worksheets("PivotTable").PivotTables("PivotTable1").ChangePivotCache (UpdatedCache)

End Sub


Note that by using .CurrentRegion, if the data is incomplete, the selection may occur incorrectly.

Delete PivotTable

We can delete the PivotTable simply by deleting the tab where it is present.

Sheets("PivotTable").Delete


Or we can delete it individually by using the .Clear method in the .TableRange2 property.

Sub DeletePivotTable()

Worksheets("PivotTable").PivotTables("PivotTable1").TableRange2.Clear

End Sub


SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2021 SuperExcelVBA | ABOUT