In this article I will explain how you can use VBA for Excel to create pivot tables.

Create New Pivot Table:

The following code will create an empty pivot table. The function receives as input a source range, and an output range. The source range is the range where the pivot tables data comes from. The output range is the where the pivot table will be created. The output range is basically just one cell:

'''
'Creates a pivot table
'rngSource: The source range for the pivot table
'rngDestincation: The destination range for the pivot table
'The function returns a pivot table object
Public Function AddPivotTable(ByRef rngSource As Range, ByRef rngDestination As Range) As PivotTable
Dim strSourceSheetName As String
Dim strSourceRange As String
Dim objPivotCache As PivotCache
Dim objPivotTable As PivotTable
Dim strDestSheetName As String
Dim strDestRange As String
Dim objWorkbook As Workbook

'the name of the source worksheet
strSourceSheetName = rngSource.Worksheet.Name
'the string representation of the source range
strSourceRange = strSourceSheetName & "!" & rngSource.Address
'the name of the destination worksheet
strDestSheetName = rngDestination.Worksheet.Name
'the string representation of the destination worksheet
strDestRange = strDestSheetName & "!" & rngDestination.Address
'the workbook of the source worksheet
Set objWorkbook = rngSource.Worksheet.Parent
'creates a new pivot table
Set objPivotTable = objWorkbook.PivotCaches.Create(xlDatabase, strSourceRange, xlPivotTableVersion14).CreatePivotTable( _
rngDestination, "PivotTable5", , xlPivotTableVersion14)

'returns the newly created workbook
Set AddPivotTable = objPivotTable
End Function

Calling Context:

Dim objPivotTable As PivotTable

Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1"))

Consider the following data: By using the example above an empty pivot table will be created in sheet2:

Adding Row Labels:

You can add row labels to the pivot table using the code below:

Dim objPivotTable As PivotTable

Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1"))

With objPivotTable.PivotFields("Date")
    .Orientation = xlRowField
    .Position = 1
End With

In the code above “Date” is the field that will become the row label.
Result:

Adding Column Labels:

You can add column labels to the pivot table using the code below:

Dim objPivotTable As PivotTable

Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1"))
With objPivotTable.PivotFields("Last Name")
    .Orientation = xlColumnField
    .Position = 1
End With

Where “Last Name” is the field that will become the column label.
Result:

Adding Page Filters:

You can add page filters to the pivot table using the code below:

Dim objPivotTable As PivotTable

Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1"))

With objPivotTable.PivotFields("Last Name")
    .Orientation = xlPageField
    .Position = 1
End With

Where “Last Name” is the field that will become the page filter.
Result:

Adding Value Fields:

The best method to adding value fields would be to use the macro recorder to get the required code. Then either replace ActiveSheet.PivotTables("PivotTable5") with the pivot table object, or ensure that the worksheet with the pivot table is the active sheet.

Filtering Fields:

Consider the following pivot table on sheet2. Lets say we want to filter the row labels to only show Anthony and Aura:

The code below will filter all fields except Anthony and Aura:

Dim objPivotTable As PivotTable
Dim i As Long

Set objPivotTable = Sheet2.PivotTables.Item(1)
'loop through the pivot items
For i = 1 To objPivotTable.PivotFields("First Name").PivotItems.Count
    'check their value
    If objPivotTable.PivotFields("First Name").PivotItems(i).Name <> "Augustina" And _
    objPivotTable.PivotFields("First Name").PivotItems(i).Name <> "Aura" Then
        objPivotTable.PivotFields("First Name").PivotItems(i).Visible = False
    Else
        objPivotTable.PivotFields("First Name").PivotItems(i).Visible = True
    End If
Next i

Result: