In this tutorial series, you will learn how to automate Pivot Table in Excel using VBA.

Pivot Table is probably one of the most useful and powerful features in Excel, especially for reporting and data analysis. Using pivot table, we can extract useful information that is hard to see from a raw data source itself. And VBA, being the built-in programming language of an Excel application, which allow us to automate and streamline many things we do with Pivot Table.

How to enable Developer Tab:

Download Financial Sample Excel Workbook:

Source Code:  

Sub Create_PivotTable()
    Dim wb As Workbook
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim LastRow As Long, LastColumn As Long
    Dim SourceDataRange As Range
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    'On Error GoTo errHandler
    Set wb = ThisWorkbook
    Set wsTarget = wb.Worksheets("Report")
    '// Step 1. Define my pivot table data source
    Set wsSource = wb.Worksheets("Source Data")
    With wsSource
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
        '// Define SourceDataRange object
        Set SourceDataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    End With
    '// Step 2. Create Pivot Cache
    Set PTCache = wb.PivotCaches.Create(xlDatabase, SourceDataRange)
    '// Step 3. Create Pivot Table
    Set PT = PTCache.CreatePivotTable(wsTarget.Range("C5"), "Sales Report XYZ")
    With PT
        '// Show GrandTotals
        .ColumnGrand = False
        .RowGrand = False
        .RowAxisLayout xlTabularRow
        .TableStyle2 = "PivotStyleMedium10"
        '.TableStyle2 = "PivotStyleLight10"
        ' Layout & Format Tab
        .MergeLabels = True
        .DisplayErrorString = True
        .ErrorString = "Error Value"
        .DisplayNullString = True
        .NullString = "Empty Value"
        .HasAutoFormat = False
        .PreserveFormatting = True
        '// Totals & Filters
        .RowGrand = False
        .ColumnGrand = True
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        '// Display
        .ShowDrillIndicators = True
        .DisplayContextTooltips = True
        .DisplayFieldCaptions = False
        .InGridDropZones = False
        .FieldListSortAscending = False
        '// Printing Tab
        .PrintDrillIndicators = False
        .RepeatItemsOnEachPrintedPage = True
        .PrintTitles = True
        '// Data
        .SaveData = True
        .EnableDrilldown = True
        .PivotCache.RefreshOnFileOpen = True
        '// Alt Text
        .AlternativeText = "Sales Summary2"
        .Summary = "Sales Summary Description"
        ' Add Pivot Fields
        '// Filters
        With .PivotFields("Product")
            .Orientation = xlPageField
            .EnableMultiplePageItems = False
        End With
        With .PivotFields("Year")
            .Orientation = xlPageField
            .EnableMultiplePageItems = False
        End With
        '// Rows Section
        With .PivotFields("Country")
            .Orientation = xlRowField
            .Subtotals(1) = False
            .Subtotals(4) = True
        End With
        With .PivotFields("Segment")
            .Orientation = xlRowField
        End With
        '// Columns
        With .PivotFields("Month Name")
            .Orientation = xlColumnField
        End With
        '// Values
        With .PivotFields("Sales")
            .Orientation = xlDataField
            .Function = xlSum
            .NumberFormat = "$#,##0.00"
        End With
        With .PivotFields("Profit")
            .Orientation = xlDataField
            .Function = xlAverage
            .NumberFormat = "$#,##0.00"
        End With
        '// Group by Values Level
        With .DataPivotField
            .Orientation = xlRowField
            .Position = 3
        End With
    End With
    Set PT = Nothing
    Set PTCache = Nothing
    Set SourceDataRange = Nothing
    Set wsSource = Nothing
    Set wsTarget = Nothing
    Set wb = Nothing
Exit Sub
    MsgBox "Error: " & Err.Description, vbExclamation
    GoTo CleanUp
End Sub

Sub Delete_PT_Test()
    Call Delete_Pivot_Tables("Report")
    Call Delete_Pivot_Tables("Test2")
End Sub

Private Sub Delete_Pivot_Tables(ByVal worksheet_name As String)
    Dim ws As Worksheet
    Dim PT As PivotTable
    On Error GoTo errHandler
    Set ws = ThisWorkbook.Worksheets(worksheet_name)
    '// iterate each pivot table in the worksheet
    For Each PT In ws.PivotTables
    Next PT
End Sub