Pivot Tables in Excel VBA – Explained with Examples!

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
By |March 1st, 2013|Excel VBA|19 Comments

About the Author:

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.

19 Comments

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

    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 - Reply

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

      Thanks-Valli!

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

    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 - Reply

      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 - Reply

    Hi,

    I wanted to creat pvot table, Based on pivot field creteria tab.

    for example.In sheet 2 i have pvot feild list Field1–Label1(Geography),Label2-(Industry),Label3-(Sector),Data ColumnsLabel1- (Cost),Label2(Value).In Sheet1 Data which is not constant.The size of the data keep on changing.

    How would you do this.

    Thanks
    Faheem

  4. Vishal May 12, 2015 at 6:18 PM - Reply

    Hi,

    I have a lot of pivots for which show the data for 5 weeks. Now every new week, I have to take the oldest week number out and select the new week in. Is there a VB script to do this?

    • PNRao May 13, 2015 at 8:42 PM - Reply

      Hi Vishal,

      You can use the below code to loop through the pivot items and select the a Pivot Item:

      Sub LoopthorughThePivotTableAndSelectAnItem()
      Set pTable = ActiveSheet.PivotTables(1)
      Set pField = pTable.PivotFields("Cat")
      pvtValToSlect = 6 ' Week number to be selected
      'first: select the required item
      For Each PItem In pField.PivotItems
      If CStr(PItem.Caption) = CStr(pvtValToSlect) Then PItem.Visible = True
      Next
      'then: unselect the remaining items
      For Each PItem In pField.PivotItems
      If CStr(PItem.Caption) <> CStr(pvtValToSlect) Then PItem.Visible = False
      Next 
      End Sub
      

      Thanks-PNRao!

  5. Pradeep SB May 27, 2015 at 11:02 PM - Reply

    Please could i know how can i run this program

  6. Santosh Hanamgonda September 26, 2015 at 6:59 AM - Reply

    Hello

    I have coded to for pivot table I am getting correct table still I want some changes in that

    1. same macro using 2nd time than should delet last pivot table
    2. I want chnges in pivot table heading color and cell color
    3. I need border for pivot table.
    4. I want to change “grangd total” to “total work Completed”.
    5. I want add comment below pivote table as work completed before 5pm.

    Now coded like these

    Sub sbCreatePivot()
    ‘Declaration
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    ‘If “Pivot” worksheet already exists, delete it

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(“Pivot”).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ‘Adding new worksheet
    Set ws = Worksheets.Add

    ‘Creating Pivot cache
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, “Sheet1!R1C1:R13C8”)

    ‘Creating Pivot table
    Set pt = pc.CreatePivotTable(ws.Range(“B3”))

    ‘Setting Fields
    With pt
    ‘set row field
    With .PivotFields(“Type”)
    .Orientation = xlRowField
    .Position = 1

    End With

    ‘set data field
    .AddDataField .PivotFields(“work count”), ” work count”, xlSum
    End With
    End Sub

    Date from Type IteM# work App weight serial
    12/23/2015 x A 2312 1 A 233 31123
    12/23/2015 y B 3 2 b 213 123
    12/23/2015 z C 21 3 c 313 3123
    12/23/2015 n D 321 1 d 13 313
    12/23/2015 m E1 12 2 a 13 3
    12/23/2015 f B 1231 3 s 13 12
    12/23/2015 r C 213 4 s 13 31
    12/23/2015 n A 123 2 d 31 3
    12/23/2015 e D 13 4 s 2 12
    12/23/2015 s F 123 20 d 123 3
    12/23/2015 x G 31 2 a 3 12

    above table I need colums are Type and work.

    Now I am geeting below shown

    Row Labels Sum of Work
    A 3
    B 5
    C 7
    D 5
    E1 2
    F 20
    G 2
    (blank)
    Grand Total 44

    can you you help me out to make required changes

  7. Santosh Hanamgonda September 26, 2015 at 7:02 AM - Reply

    data heading are Date, from, Type, IteM#, work, App .weight, serial

    Pivot table headings are Row Labels, Sum of Work

  8. December 13, 2015 at 10:02 AM - Reply

    Hai sir,
    i need your help, can u help me i am explain here what i need, actually i am doing a business so i need monthly outstanding report with easily i am using excel for entering details of sales so how can i get each party report i need party name qty of sale totl amount of each date like pls help

  9. Laxman kumar January 31, 2016 at 6:11 PM - Reply

    Pivot table’s example that given here is so useful to learn. Thanx alot

  10. krishna February 11, 2016 at 6:27 PM - Reply

    Hi there,

    I am trying to use vba code to select a single field from my pivot then go back to the pivot to select multiple fields from the filter.

    Any ideas?

    Thanks
    Krishna

  11. dash March 3, 2016 at 2:24 AM - Reply

    how to select the filter

  12. Krishnan July 27, 2016 at 11:41 AM - Reply

    Hi guys. Need a quick help.

    For my monthly reporting, how can I make the pivot to add a new month by itself, as and when a new month raw data is added??

    Thanks
    Krishnan

  13. Jana September 22, 2016 at 6:42 AM - Reply

    Hi.
    This is very helpful.
    I have created a complex pivot table and it works perfectly when I use the code in Excel 2010.
    But when I tried the same code in Excel 97-2003, it errors on creating the pivot table.
    I wish to create it in Excel 97-2003 because pivot table is easier to format in this version. Please help out. Thank you very much

    • Jana September 22, 2016 at 6:48 AM - Reply

      ….correction, I used the code in Excel 2013 (not 2010).

  14. ricky May 25, 2017 at 10:01 PM - Reply

    Hai
    i need some help

    we all know that in the user form, we can load or show image or picture, and load or show the data with list object.. etc..
    but how can i show a pivot table in the user form ? which object i have to use ? can i use list object (in toolbox)? and how can i use it ? give me an example code..
    and please tell me how to add a calendar object in the toolbox..
    thanks

  15. Bala June 9, 2017 at 6:09 PM - Reply

    Hai Valli,

    I am new to vba. I have some requirement based on manipulating data. I have spent time on googling to complete the things, but i can’t understand by their way of approach. I recently found your site, you are providing good things to learn. I need a sample excel macro file that satisfy the below listed operations,

    1) Copy multiple sheets entire data to one workbook
    2) Add some coulmns
    3) Make some multiple column filter with multiple criteria
    4) Fill cell based on filtered results to new column
    5) Make VLOOKUP with opened another sheet data
    5) Finally form some PIVOT TABLE .

    Please guide me how to do the above.

    Thanks in ADVANCE…..

Leave A Comment


Related pages


excel if statement examples textexcel unhide sheetms excel 2007 formulas with examples pdf in hindiaccess vba multidimensional arrayconcatenate in excel 2010worksheetfunction.vlookuphow do i unlock an excel spreadsheetunprotect shared workbook forgot passwordexcel 2007 vba editormacro learning in excelit business analyst interview questions and answers pdfdeveloper tab excel 2010excel simple macrohyperlink functionexcel advanced filter exampleproject dashboard templatevba excel isblankvba excel objectmicrosoft word project plan templatearrays in vbaworksheet functions in vbaexcel vba open file and copy datavba excel commentchoose formula in excelhow do i concatenate in excelexcel vba best practicesvba excel switchchart wizard in excel 2007sorting formula in excelinterview questions and answers freshersvba access status barvba loop syntaxcombine multiple excel files into one worksheetrun excel macroexcel 2007 autosave locationremove blank rows excel 2010excel pie chart titleexcel macro close excelv lookup h lookup pivot tablehow to use iferror in excel 2010excel bar graph tutorialstacked line chart excelrecording macros in excelvba applicationhow to create an excel table with headerschar function excelcombo boxes in excelvba coding basicshow to lock spreadsheetadodb.connection excelvisual basic hyperlinkvb net excel set column widthhlookup function in excelunprotect workbook without passworddelete empty cells in excelexcel hidden columnswhere can i find developer tab in excel 2010excel vba range selectautofit column width excel 2010delete blank rows excelis there a way to find duplicates in excelwhere is developer tab in excel 2013vba excel learnlearn access vbainsert radio button in excelcool excel macro tricksvb scripting interview questions and answers pdfformula in pivot table excel 2010how to unlock an excel spreadsheetms access adodb connectionvba simple examplesexcel vba range variable