Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

VBA To Create a Pivot Table

Before I hand over this guide to you for 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 wrote a macro code 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.

So today, I will show you a simple way to automate your pivot tables using a macro code.

For this, I have split the entire process into 8 steps. And, after following these steps you will able to automate your all the pivot tables.​

Macro Codes To Create A Pivot Table

Before we start to create a pivot table with VBA, we need to declare variables which we need to use in our code.

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 the above code, we 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 and LastCol to get the last row and column of our data range

Insert a New Worksheet

To create a pivot table, we need to insert a new worksheet and for this we need to add below macro code.

insert a new worksheet to use vba to create pivot table in excel
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 help us to delete any old worksheet which has pivot table and then it will create a new blank worksheet with the same name.

Define Data Range

Now, next thing is to define data range which we will use as a data source for our pivot table.

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 the above code, LastRow and LastCol defines last row and column of the data range. And then, we have named the data range PRange.

With this, we don’t have to change data source every time while creating the pivot table.

Create Pivot Cache

In excel 2000 and above, before creating a pivot table we need to create a pivot cache to define data source. And, the code is:

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

Above code will create a pivot cache which we can use further to create our pivot table.

Insert a Blank Pivot Table

Once you create a pivot cache, next step is to insert a blank pivot table. And, the code is here.

insert a blank pivot to use vba to create pivot table in excel
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Above code will create a blank pivot table and use pivot cache as data source.

Insert Row and Column Fields

After creating a blank pivot table, we need to insert row and column fields. For this, use below code.

insert row column fields to use vba to create pivot table in excel
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
.Orientation = xlRowField
.Position = 2
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
.Orientation = xlColumnField
.Position = 1
End With

In the above code, we have used Year and Month as row fields. And, Zone as a column field. You can define position of a field by using position numbers.

Insert Data Field

Next step is to define data field and we need to use below code.

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

In the above code, we have used Amount as a data field and defined sum as a function for values and we have also defined the number format of the values.

And, if we want to add more than one data field we can use the same code twice and define the position for the both fields.

Format Pivot Table

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

use vba to create pivot table in excel formatting

Above code will apply row strips and "Pivot Style Medium 9" style to our pivot table and we can also use another style.

Sample File

download sample excel file

Conclusion

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

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.


  • 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

  • FuriousDK

    Hi, I used this data to create my pivot tables. This worked perfectly until I had a large number of rows. I have 220,000 rows.
    I can create the pivot manually but not using the macro. If I reduce this same data down to 45,000 rows it does work perfectly. Any ideas?

    • Puneet Gogia

      Hello FuriousDK,

      I’ve used this code for 131897 rows without any problem. I’m wondering why you got that issue. Please share your data if possible.

  • Em

    Hi there! Just used your code and it worked perfectly! Thank you so much for sharing and then breaking it down so that I can actually learn from it!

    • Puneet Gogia

      I’m so glad you liked it.

  • vikas joshi

    How will open a exl file from particular location with a only keyword of that file, and save it to other location using vba?

  • Miklos Kallo

    A couple of notes:

    1) Inadvertently 2 rows were joined in the code:
    ActiveSheet.Name = “PivotTable”Application.DisplayAlerts = True

    2) Instead of these:
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = “PivotTable”
    Set PSheet = Worksheets(“PivotTable”)

    I would simply and more elegantly use:
    Set PSheet = ActiveWorkbook.Worksheets.Add
    PSheet.Name = “PivotTable”

    3) I would replace:
    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)

    with a simpler approach:
    Set PRange = DSheet.Cells(1, 1).CurrentRegion

    4) You create PTable as a Pivot table object but only use it once, here:
    Dim PTable As PivotTable

    The reason for this is probably that (possibly in newer versions of Excel?) it doesn’t even get assigned a real value, it stays Nothing.
    You actually can comment this line out and the code will still run:
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 1), TableName:=”SalesPivotTable”)

    I suppose you are aware of this, since you use ActiveSheet.PivotTables(“SalesPivotTable”) wherever you would normally use PTable.

    Actually PCache, too, stays Nothing so this approach doesn’t really need those variables.

    I find this approach more suitable.

    Dim objTable As PivotTable, objField As PivotField

    Set objTable = PSheet.PivotTableWizard(SourceType:=xlDatabase, _
    SourceData:=PRange, _
    TableDestination:=PSheet.Cells(2, 2))
    ‘if you don’t want grand totals, you can add: , RowGrand:=False, ColumnGrand:=False)

    Set objField = objTable.PivotFields(“Year”)
    With objField
    .Orientation = xlRowField
    .Position = 1
    ‘if you don’t want subtotals, you can add: .Subtotals(1) = False
    ‘ you can change the name too .Caption = “Whatever”
    End With

    Set objField = objTable.PivotFields(“Month”)
    With objField
    .Orientation = xlRowField
    .Position = 2
    End With

    Set objField = objTable.PivotFields(“Zone”)
    With objField
    .Orientation = xlColumnField
    .Position = 1
    End With

    Set objField = objTable.PivotFields(“Amount”)
    With objField
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = “#,##0”
    .Name = “Revenue ”
    End With