Pivot Tables in Excel VBA – Explained with Examples!

Pivot tables in Excel VBA helps us to create summary tables for analyzing our data. We can create different aggregates, change the formats and create the calculated fields. We will see Pivot Table operation using Excel VBA.

Creating Pivot Tables in Excel VBA

This example code will show you how to create Pivot tables using Excel VBA. Instructions are commented in the code to understand the use of each statement.

'Example Data in Sheet1
'Department  Region     Profit
'109            5         119000
'107            3         64000
'109            2         12000


Sub sbCreatePivot()

'Declaration
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable

'Adding new worksheet
Set ws = Worksheets.Add

'Creating Pivot cache
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R10C3")

'Creating Pivot table
Set pt = pc.CreatePivotTable(ws.Range("B3"))

'Setting Fields
With pt
    'set row field
    With .PivotFields("Department")
    .Orientation = xlRowField
    .Position = 1
    End With
    
    'set column field
    With .PivotFields("Region")
    .Orientation = xlColumnField
    .Position = 1
    End With
    
    'set data field
    .AddDataField .PivotFields("Profit"), "Sum of Profit", xlSum
End With

End Sub

Create Pivot Chart using Excel VBA

This example will show you how to create Pivot chart using Pivot table. Instructions are commented in the code.

Sub sbPivotChartInNewSheet()

'declaration
Dim pt As PivotTable, ptr As Range, cht As Chart
    
'If no pivots exit procedure
    If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
    
'setting piovot table
 Set pt = ActiveSheet.PivotTables(1)
 Set ptr = pt.TableRange1
 
' Add a new chart sheet.
Set cht = Charts.Add
    With cht
        .SetSourceData ptr
        .ChartType = xlLine
    End With

End Sub

Create Pivot Column Chart using Excel VBA

This is same as the above, the only change is Chart type. So we can use all chart options to Pivot charts.

Sub sbPivotChart()

Dim sh As Shape

'If no pivots exit procedure
If ActiveSheet.PivotTables.Count = 0 Then Exit Sub

'setting piovot table
 Set pt = ActiveSheet.PivotTables(1)
 Set ptr = pt.TableRange1

'Add chart
Set sh = ActiveSheet.Shapes.AddChart
sh.Select
 With ActiveChart
        .SetSourceData ptr
        .ChartType = xl3DColumn
End With

End Sub

Create Calculated Pivot Field in Excel VBA

We may not to store the calculated fields in the table or Excel worksheet. We can create the calculated field in the Pivot tables as shown below. So that we can save the space and easity manage the and analyse the data.

Sub sbCreateCalculatedField()

'Example Data in Sheet1
'Department  Region     Profit
'109            5         119000
'107            3         64000
'109            2         12000

'Declaration
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
'Adding new worksheet
Set ws = Worksheets.Add

'Creating Pivot cache
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R10C3")

'Creating Pivot table
Set pt = pc.CreatePivotTable(ws.Range("B3"))

'Setting Fields
With pt
    'set row field
    With .PivotFields("Department")
    .Orientation = xlRowField
    .Position = 1
    End With
    
    'set column field
    With .PivotFields("Region")
    .Orientation = xlColumnField
    .Position = 1
    End With
    
    '***Create Calculated Field
    .CalculatedFields.Add "Savings", "=Profit*.1"
    
    'set data field
    .AddDataField .PivotFields("Savings"), "Sum of Savings", xlSum
End With

End Sub

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource

Excel VBA Reference	Project Management Reference
VBA Reference: VBA Code Explorer VBA Excel Application VBA Excel Workbook VBA Excel Worksheet VBA Excel Range VBA ActiveX Controls VBA Userforms VBA Projects Excel Reference: Excel Tutorial Microsoft Excel 2003 Microsoft Excel 2007 Microsoft Excel 2010 Microsoft Excel 2013 Microsoft Excel 2016	What is a Project? Project Appraisal Project Management Project Plan Project Resource What is Gantt Chart? Excel Templates Excel Project Management Templates PowerPoint Project Management Templates MS Word Project Management Templates Excel Basics Tutorials Excel VBA Tutorials Excel Functions and Formulas

External VBA Reference

Excel VBA Reference

Project Management Reference

VBA Reference: Getting Started with VBA in Office VBA for MS Office Application: Complete References Excel VBA Reference VBA Tutorials VBA Macros VBA Functions Excel Reference: Microsoft Excel Reference Excel Formulas Excel 2016 Excel Functions Excel Quick Tutorials

Project Management Methodologies MS Project Templates Excel, Word, PowerPoint Timeline Templates Excel, Word, PowerPoint Planners and Trackers Microsoft Excel Templates MS PowerPoint Excel Templates MS Word Templates MS Office Schedules Templates Excel, Word, PowerPoint Agenda Templates Excel, Word, PowerPoint Invoices Templates Excel, Word, PowerPoint Business Plans Templates Excel, Word, PowerPoint Financial Management Templates Excel, Word, PowerPoint Minutes Templates