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

Premium Project Management Templates

50+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

50+ Project Management Templates Pack

Excel PowerPoint Word

VIEW DETAILS

Ultimate Project Management Template – Advanced

Excel Template

VIEW DETAILS

ULTIMATE PROJECT MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

20+ Excel Project Management Pack

Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack

PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack

Word Templates

VIEW DETAILS

By Published On: March 1st, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

20 Comments

  1. Lisa August 9, 2014 at 7:26 AM

    Hi Valli,
    you are so good..
    would you be able to demonstrate pivot tables with conditional formatting in vba .
    say I would like to format all values in . PivotFields(“Percent of “) for anything between 10 and 25 in red , 20 and 50 in blue and 50 to 75 in yellow.
    How would you do this?

    • Valli August 17, 2014 at 11:29 AM

      Hi Lisa,
      Thanks for your comments. I will provide an example ASAP.

      Thanks-Valli!

  2. Durga February 5, 2015 at 7:35 PM

    Hi Lisa,

    I need a quick help. I have a complicated Pivot table, with main status showing on dates for 5 different columns. The date of each column had to be compared with todays date and status of the template say example 1, then as below

    as of today(02.05.15) if I look at the column 1 date(01.14.15) and status = 1, I have to mark the cell as RED color.

    Please help.

    Thanks
    Durga.

    • PNRao February 5, 2015 at 8:44 PM

      Hi Durga,

      You can do this using Conditional formatting. You can also use the below VBA code to check the pivot row labels and highlignt in red if it matches the criteria:

      Sub HilightPivotCellsBasedOnCondition()
          For Each cell In ActiveSheet.PivotTables(1).RowRange ' this loop through the each cell in the pivot table row labels/ side labels
              If Format(cell.Value, "mm.dd.yy") = Format(Now(), "mm.dd.yy") Then cell.Interior.Color = RGB(225, 0, 0)
          Next
      End Sub
      

      Thanks-PNRao!

  3. Faheeem February 13, 2015 at 12:03 AM