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.
LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Ultimate Project Management Template – Advanced
Excel Template
ULTIMATE PROJECT MANAGEMENT TEMPLATE
Excel Template
20+ Excel Project Management Pack
Excel Templates
20+ PowerPoint Project Management Pack
PowerPoint Templates
10+ MS Word Project Management Pack
Word Templates
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?
Hi Lisa,
Thanks for your comments. I will provide an example ASAP.
Thanks-Valli!
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.
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:
Thanks-PNRao!