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.

FIRST STEPS

FUNDAMENTALS

RANGE

USERFORM

DEEPENING