Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

Use VBA To Create Pivot Table In Excel

    how to create a pivot table with vba

    Before I hand over this guide to you about using VBA to create a pivot table, let me confess something.

    I have learned using VBA just three years back. And, the first time when I had used a macro to create a pivot table, it was a failure.

    Since then I have learned more from my bad coding rather than from the codes which actually work. That’s why I am trying to create an Ultimate VBA Guide (Small E-Book) to share right ways to learn VBA.

    I have split the process of using VBA to create a pivot table in 8 steps. And, after following these steps you will able to automate your all the pivot tables just like below example.

    Macro Codes Use VBA To Create Pivot Table In Excel

    Steps to create a pivot table using VBA.

    1. Declare Variables
    2. Insert a New Worksheet
    3. Define Data Range
    4. Create Pivot Cache
    5. Insert a Blank Pivot Table
    6. Insert Row & Column Fields
    7. Insert Data Fields
    8. Format Pivot Table

    Now, let’s understand each step to use to create a pivot table with VBA.

    Declare Variables

    Before we start to create a pivot table with VBA, we have to declare variables that we can use in our code.

    Declare Variables To To Use VBA To Create Pivot Table In Excel

    'Declare Variables
    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long

    In above code, I have declared PSheet to create a sheet for a new pivot table, DSheet to use as a data sheet, PChache to use as a name for Pivot Table Cache, PTable for use as a name for our pivot table, PRange to define source data range & LastRow & LastCol to get the last row & column of our data range.

    Insert a New Worksheet

    Before I create a new pivot table I have to insert a new worksheet for my Pivot Table.Insert A New Worksheet To Use VBA To Create Pivot Table In Excel

    Here is the code

    'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("PivotTable").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "PivotTable"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("PivotTable")
    Set DSheet = Worksheets("Data")

    Above code will first delete any old worksheet which has pivot table & then creates a new blank worksheet with the same name.

    Important Tip: Refresh All Pivot Tables

    Define Data Range

    Now, next thing is to define data range which I want to use for my Pivot Table.

    'Define Data Range
    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

    In above code, LastRow & LastCol will define last row & column of the data range. And then I have named that data range as PRange.

    Doing this has a benefit that you don’t have to change data source every time while creating the pivot table.

    Create Pivot Cache

    Before you create a Pivot Table, you need to create pivot cache for that. In excel 2000 & above you have to create a pivot cache to define your data source.

    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ 
    TableName:="SalesPivotTable")

    Above code will, will create a pivot cache which I can use to create my pivot table.

    Insert a Blank Pivot Table

    Once you create a pivot cache, next step is to insert a pivot table.

    Insert A Blank PivotTo Use VBA To Create Pivot Table In Excel

    And, the code is here.

    'Insert Blank Pivot Table
    Set PTable = PCache.CreatePivotTable_
    (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

    By using above code, you can create a blank pivot table & use pivot cache as a data source.

    Insert Row & Column Fields

    Once you create a blank pivot table, we have to insert row & column fields in it.

    Insert Row Column Fields Use VBA To Create Pivot Table In Excel

    'Insert Row Fields
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
     .Orientation = xlRowField
     .Position = 1
    End With
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
     .Orientation = xlRowField
     .Position = 2
    End With
    
    'Insert Column Fields
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
     .Orientation = xlColumnField
     .Position = 1
    End With

    I have used Year & Month as a row fields. And, zone as a column field. You can define positions of a field by using position numbers.

    Insert Data

    Now it’s time to insert a data field into the pivot table.

    Insert Data Field To Use VBA To Create Pivot Table In Excel

    'Insert Data Field
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")
     .Orientation = xlDataField
     .Position = 1
     .Function = xlSum
     .NumberFormat = "#,##0"
     .Name = "Revenue "
    End With

    Use above code to insert a data field in your pivot table. I have used Amount as a data field but you can change it as per your need. You can also format numbers as per your need. And, If you are using more than one data fields you have to define their positions as well.

    Format Pivot Table

    Now, it’s time to give a final touch of formatting.

    Use VBA To Create Pivot Table In Excel Formatting

    'Format Pivot Table
    ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
    ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

    By using above code, you can apply row strips and Pivot Style Medium 9.  And, your pivot table is ready.

    Just compiling all the above codes you can create a pivot table with a single click. Please download this entire code from here or you can download the sample file from link mention below.

    Sample File

    download sample file to learn more about how to create a pivot table with vba

    Conclusion

    By using this code you can automate your pivot tables. And, this is a one time set up and after that, you just need a click to create a pivot table.

    Now tell me one thing. Have you ever used VBA to create a pivot table?

    Please share your experience with me in the comment box. Also, share if you faced any problem while using this code.

    More Pivot Table Tips



    • jose luis

      hello, I m starting with your code, I ll adapt but i have a question.
      Why do i need to delete first the wsheet before creating the new pivot table, and not deleting the old one and using the sheet??
      thanks

      • Puneet Gogia

        It’s all up to you, Jose.

        If you down want to delete that sheet, you can just delete the pivot table from the sheet.

    • Jessica

      Hi I’m not sure what is happening. I used your code but subbed in my own names for the different fields. When I run the script, a new sheet is created but nothing happens. Can you please help me?

    • Jessica

      Hello, now I do see a table but as you can see from the first picture, there is no data for some reason? It is supposed to look like the second picture (with numbers under false and true and grand total). https://uploads.disquscdn.com/images/3c367864ffd28260f1430bcfaa181bff14709d91e23e47bbdeaac389d8e7e9f6.png https://uploads.disquscdn.com/images/d4c27640c0e2946293f282a4808787395bf08f4d56c119ab44f9b95a57320262.png

    • Jessica

      Hello Puneet, I got a pivot table but it is populated with zeroes (see second picture). How can I get values from it–it is supposed to look like the first picture. https://uploads.disquscdn.com/images/3c367864ffd28260f1430bcfaa181bff14709d91e23e47bbdeaac389d8e7e9f6.png https://uploads.disquscdn.com/images/1b544e4c5ba16cd59b563f17e4ffd58fd899f287d4feeade248fc9cb36321272.png

      • Puneet Gogia

        I’ll appreciate if you share your file or code.

        • Ryuzaki

          I think it’s a bit late, but all you have to do is change .Function = xlSum to .Function = xlCount

          Best of luck

    • Samir Kumar Naik

      Hi, Thanks a lot for the code. It is working fine. However I would like to create one more pivot in the same sheet. What should i do. I tried to declare and repeated the whole code, however receiving error msg. Could you please advise. Below is the code which am trying.

      Dim PSSheet As Worksheet
      Dim DSheet1 As Worksheet
      Dim PSCache As PivotCache
      Dim PTable1 As PivotTable
      Dim PSRange As Range
      Dim LastRowS1 As Long
      Dim LastColS1 As Long

      ‘Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name

      Set PSSheet = Worksheets(“PivotTable”)
      Set DSheet1 = Worksheets(“PA Classifications”)

      ‘Define Data Range
      LastRowS1 = DSheet1.Cells(Rows.Count, 1).End(xlUp).Row
      LastColS1 = DSheet1.Cells(1, Columns.Count).End(xlToLeft).Column
      Set PSRange = DSheet1.Cells(1, 1).Resize(LastRowS1, LastColS1)

      ‘Define Pivot Cache
      Set PSCache = ActiveWorkbook.PivotCaches.Create _
      (SourceType:=xlDatabase, SourceData:=PSRange). _
      CreatePivotTable(TableDestination:=PSSheet.Cells(2, 2), _
      TableName:=”Hello”)

      ‘Insert Blank Pivot Table
      Set PTable1 = PCache1.CreatePivotTable _
      (TableDestination:=PSheet1.Cells(12, 20), TableName:=”SamirPivotTable2″)

      • Puneet Gogia

        Hello Samir,

        I’m sorry for my super late reply.

        yes, you can use the same data to create a second pivot table but first you have to specify the cell in the same worksheet on which you want to insert that second pivot.

        I’ll appreciate if you share your file. Info@excelchamps.com.

        Regards
        Puneet

    • Abhinav

      Excellent Blogpost! Do you also have any such blog for Pivot Chart?

    • carcanken

      I like the code you have but one question I need help with. for the column I use dates 01/2016 through 12/2019 but would like them to show up as the name value in the fields list. how can I group these and still show each column

      • Puneet Gogia

        Please elaborate.

    • K

      Great post, so I have a question if i want to create a pivot from external excel book how can i link it? Like create pivot from there

      • Puneet Gogia

        You have to change data source to external.

    • Jonathan Grunwaldt

      Hi — I am trying to duplicate this however I am getting an error on the second to last step, where we actually create the pivot table. The error reads Compile Error: Expected: Line number or label or statement or end of statement