How to Create a Pivot Table in Excel using VBA [Macro Code]

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

I have learned using VBA just four 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.

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

When normally when you insert a pivot table in a worksheet it happens through a simple process but that entire process is so quick that you never notice what happened.

In VBA, that entire process is same just executes using a code. In this guide, I’ll show that each step and how to write a code for it.

Just look at the below example, where you can run this macro code with a button and it returns a new pivot table in a new worksheet in a flash.

Macro Codes To Create A Pivot Table

Eight Simple Steps to Write a Macro Code for Pivot Table

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

  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 and Column Fields
  7. Insert Data Field
  8. Format Pivot Table

(1). Declare Variables

The first step is to declare the variables which we need to use in our code to define different things.

'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 the above code, we have declared:

  1. PSheet: To create a sheet for a new pivot table.
  2. DSheet: To use as a data sheet.
  3. PChache: To use as a name for pivot table cache.
  4. PTable: To use as a name for our pivot table.
  5. PRange: to define source data range.
  6. LastRow and LastCol: To get the last row and column of our data range.

(2). Insert a New Worksheet

Before creating a pivot table, Excel inserts a blank sheet and then create a new pivot table there.

insert a new worksheet to use vba to create pivot table in excel

And, below code will do the same for you.

It will insert a new worksheet with the name “Pivot Table” before the active worksheet and if there is worksheet with the same name already, it will delete it first.

After inserting a new worksheet, this code will set the value of PSheet variable to pivot table worksheet and DSheet to source data worksheet.

'Insert a New Blank Worksheet
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")

If you want to change the name of the worksheet, you can replace it from the code.

(3). Define Data Range

Now, next thing is to define data range from the source worksheet.

Here you need to take care of one thing that you can't specify a fixed source range. You need a code which can identify the entire data from source sheet.

And, below is the code:

'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)

This code will start from the first cell of the data table and select up to the last row and then up to the last column.

And finally, define that selected range as a source.

(4). Create Pivot Cache

In Excel 2000 and above, before creating a pivot table you need to create a pivot cache to define the data source.

Normally when you create a pivot table, Excel automatically creates a pivot cache without asking you, but when you need to use VBA, you need to write a code for this.

And  below code is for this:

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

This code works in two way, first define a pivot cache by using data source and second define the cell address in the newly inserted worksheet to insert the pivot table.

You can change the position the pivot table by editing this code.

(5). Insert a Blank Pivot Table

After pivot cache, next step is to insert a blank pivot table.

Just remember when you create a pivot table what happens, you always get a blank pivot first and then you define all the values, columns, and row.

insert a blank pivot to use vba to create pivot table in excel

This code will do the same:

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

This code creates a blank pivot table and names it "SalesPivotTable". You can change this name from the code itself.

(6). Insert Row and Column Fields

After creating a blank pivot table, next thing is to insert row and column fields, just like you do normally.

For each row and column field, you need to write a code. Here we want to add years and month in row field and zones in the column field.

insert row column fields to use vba to create pivot table in excel

Here is the code:

'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

In this code, you have mentioned year and month as two fields. Now, if you look at the code you'll find that there is also a position number is there.

​This position number defines the sequence of fields.

​Whenever you need to add more than one fields (Row or Column) make sure to specify their position.

And, you can change fields by editing their name from code.

(7). Insert Data Field

Now, the main thing is to define value field in your pivot table.

The code for defining values is a bit different from defining row and columns because here we need to define the formatting of number, position, and function.

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

With above code, you can add amount as the value field. And this code will format values as a number with (,) separator.

We are using xlsum to sum values but you can also use xlcount and other functions as well.

(8). Format Pivot Table

In the end, you need to use a code to format your pivot table.

Normally in a pivot table there is also a default formatting and then you change that formatting. But with VBA you can define formatting style withing the code.

use vba to create pivot table in excel formatting

Code is:

'Format Pivot
TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

Above code will apply row strips and "Pivot Style Medium 9" style but you can also use another style.

Finally, you're code is ready to use.

Full Macro Code to Create a Pivot Table

Sub InsertPivotTable()
'Macro By ExcelChamps

'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

'Insert a New Blank Worksheet
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")

'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)

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

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

'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

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

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

End Sub

Sample File

Enter your name and email below to get the sample file directly into your inbox.

Conclusion

By using this code we can automate your pivot tables.

Now tell me one thing.

Have you ever used a VBA code to create a pivot table?

Please share your views with me in the comment box, I'd love to share with you and make sure to share this tip with your friends.

You Must Read these Next

  1. Group Dates in a Pivot Table: With group dates option you combine dates in weeks, month, and years which further save your time [...]
  2. Pivot Table from Multiple Worksheets: Normally when you create a pivot table you can select source data from a single table in a worksheet [...]
  3. Automatically Update a Pivot Table Range: Every time when you add new data in the source sheet you need to update the source range [...]
  4. Single Slicer with Multiple Pivot Tables: Let’s say you are working on a dashboard where you are using multiple pivot tables and if [...]
  5. Pivot Table Timeline: The best thing about a timeline filter is you don’t need to open the filter again and again, you just do it with a [...]
  6. Pivot Table Using Multiple Files: Sometimes we receive or capture our data in different workbooks and in that case creating a pivot table [...]
  7. Ranks in a Pivot Table: If we use ranking in a pivot table we can save our a lot of time and efforts and it will help us in our analysis [...]
  8. Refresh All Pivot Tables: The problem is when you have multiple pivot tables. You need to spend one second to refresh each [...]
  9. Running Total in a Pivot Table: One of the things which I like about a pivot is we can easily add running total into it, which can [...]
  10. Top 31 Pivot Table Tips & Tricks: For you, here is a list of useful tips and tricks which you can learn to make best out of this amazing tool [...]

Content Protection by DMCA.com
2018-11-16T06:13:37+00:00

96 Comments

  1. Rajguhan 3 Dec, 18 at 2:46 am - Reply

    Hi puneet,
    Its awesome! Can i get this to my email!

  2. Raghu 26 Nov, 18 at 3:53 pm - Reply

    This code does not work in Excel 2016

  3. PRAVEEN KUMAR 21 Nov, 18 at 10:34 am - Reply

    how to remove desirable variable from pivot table with vba

  4. Selvakumar 13 Nov, 18 at 7:43 pm - Reply

    Hi Puneet,

    Thanks for the awesome code. I completely believe it is useful for many. But I am getting error on running this code. I cant find the mistake I did while changing the code to match my sheet. Please help me to find the problem. The output for is a Pivot with Row and Column Fields with no Datafield. Below is my code:

    Sub Total_Tickets_Created()
    ‘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
    Dim twb As Workbook
    Set twb = ThisWorkbook

    ‘Insert a New Blank Worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    twb.Worksheets(“Total Tickets Created”).Delete
    Sheets.Add After:=Sheets(2)
    ActiveSheet.Name = “Total Tickets Created”
    Application.DisplayAlerts = True
    Set PSheet = Worksheets(“Total Tickets Created”)
    Set DSheet = Worksheets(“Case Advanced Find View”)

    ‘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)

    ‘Define Pivot Cache
    Set PCache = twb.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
    TableName:=”Total Tickets”)

    ‘Insert Blank Pivot Table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 1), TableName:=”Total Tickets”)

    ‘Insert Row Fields
    With Sheets(“Total Tickets Created”).PivotTables(“Total Tickets”).PivotFields(“Priority”)
    .Orientation = xlRowField
    .Position = 1
    End With

    ‘Insert Column Fields
    With Sheets(“Total Tickets Created”).PivotTables(“Total Tickets”).PivotFields(“Created Month”)
    .Orientation = xlColumnField
    .Position = 1
    End With

    ‘Insert Data Field
    With Sheets(“Total Tickets Created”).PivotTables(“Total Tickets”)
    .PivotFields (“Case Number”)
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = “#,##0”
    .Name = “Total Tickets”

    .ShowTableStyleRowStripes = True
    .TableStyle2 = “PivotStyleMedium9”

    End With

    End Sub

  5. Tim 17 Oct, 18 at 11:28 am - Reply

    Thank you so much for this. Saved me a huge amount of time.

  6. Wernstrom 17 Oct, 18 at 6:39 am - Reply

    This is amazingly useful! I have been able to use it for my own work and it’s great. However, I do want to be able to nominate certain columns to go in the “Value” box of the pivot table (I need to count some cells). Whenever I try to do a “Count”, it seems to override the “insert row fields” function, so all I get is a count of the whole dataset, rather than a specific row. Any thoughts on how to get around this?

    Many thanks, you da bomb

  7. Kendra 4 Oct, 18 at 8:18 pm - Reply

    This article was so helpful! I was able to adapt the names to pull a table for my data without an issue. Now my only question is, how can I get it to pull a second pivot table from the same data on to the same worksheet?

    • Puneet 5 Oct, 18 at 1:24 pm - Reply

      Like, you want to create a new pivot table from the same source data and which need to have different fields? Correct me, if I’m wrong.

  8. Abdul Haq 26 Sep, 18 at 1:53 pm - Reply

    How to apply filter in the field of pivot table.. Just as you added Row and Column data.

    Apart from the Code works awesome… Thanks Puneet 🙂

  9. Soma 26 Sep, 18 at 1:45 pm - Reply

    Thanks for this post! Clear and works

  10. Scott Gardner 4 Sep, 18 at 3:42 pm - Reply

    Hi Puneet, I cannot get this code to create the pivot table. There are no errors when I try to run the code and all of the table names and values are correct. I also put the data into table format and that doesn’t seem to be the issue. Any assistance is greatly appreciated!

    • Puneet 6 Sep, 18 at 5:53 am - Reply

      Can I have a mail puneet[@]gogia[.]me ?

  11. Vik 21 Aug, 18 at 3:50 pm - Reply

    Thank you man! You are awesome!

    • Puneet 21 Aug, 18 at 3:54 pm - Reply

      You’re Welcome.

  12. Ravan 17 Aug, 18 at 2:04 pm - Reply

    Hi Punnet.Thanks for sharing this simple and useful code.
    I am facing issue that if I work with less column data code works but when I add more columns then code fails i.e.Pivot table sheet is created but table is not created. Please guide.

    • Puneet 20 Aug, 18 at 8:07 am - Reply

      Make sure you are using an Excel table and using its name in source reference. Knock me back if you need further help.

  13. Chrissi 17 Jul, 18 at 7:33 pm - Reply

    This was amazing. Thank you so much!

    • Puneet 18 Jul, 18 at 7:53 am - Reply

      I’m so glad you liked it.

  14. duncanwil 7 Feb, 18 at 11:14 pm - Reply

    Thanks for posting that Puneet and I got it to work very easily. I have been using Excel for 30 years but never needed to learn VBA. Now I am but I have a question:

    I can see the beauty of putting a macro button on the data sheet and I can see the simplicity of using VBA to find the data as the basis of the PT, however, as a test for myself, I manually created the same pivot table as you did, having first converted the data range to an Excel Table and with about four or five clicks I get my result.

    I don’t say you are wasting your time but what is the big advantage of doing this in VBA?

  15. Ishanka 23 Jan, 18 at 6:30 am - Reply

    Send me the Sample file pls.
    ishankacrashed93@outlook.com
    Ishanka

  16. Ishanka 23 Jan, 18 at 6:29 am - Reply
  17. Scott Melton 5 Jan, 18 at 6:55 pm - Reply

    THANK YOU THANK YOU THANK YOU for posting this!!! You just saved me multiple days of tedious manual effort.

    The explanations are easy to understand and conceptualize. This post was a godsend

  18. rich franz-under 21 Dec, 17 at 5:44 pm - Reply

    Hi Puneet,

    I am getting the same error as “Jonathan Grunwaldt • 10 months ago”. A compile error in the second to the last step where the xlDataField is an invalid qualifier. I am using excel 2013. https://uploads.disquscdn.com/images/f5e324c5947ce1470b5d3c263b69f37ecffef973a98d89a566e81b237ac7d55e.png

  19. Sachin Gupta 20 Dec, 17 at 7:41 am - Reply

    Hi , thank you for the important lesson , i am new to VBA , i did try the code however it only inserts a new worksheets with name Pivot Table , nor creating the Cache neither capturing the data ,

    please help

    Sub InsertPivotTable()

    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

    ‘=========================================
    ‘ INSERT NEW WORKSHEET
    ‘=========================================
    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”)

    ‘=========================================
    ‘ DEFINE DATE 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)

    ‘=========================================
    ‘ Define Pivot Cache
    ‘=========================================

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

    ‘=========================================
    ‘ Insert Blank Pivot Table
    ‘=========================================

    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 1), TableName:=”PivotTable”)

    ‘=========================================
    ‘ Insert Row Fields
    ‘=========================================

    With ActiveSheet.PivotTables(“PivotTable”).PivotFields(“Last_Touch_User_Name”)
    .Orientation = xlRowField
    .Position = 1

    End With

    ‘=========================================
    ‘ Insert COLUMN Fields
    ‘=========================================

    With ActiveSheet.PivotTables(“PivotTable”).PivotFields(“Action_Code”)
    .Orientation = xlColumnField
    .Position = 1
    End With

    With ActiveSheet.PivotTables(“PivotTable”).PivotFields(“Result_Code”)
    .Orientation = xlColumnField
    .Position = 2
    End With

    ‘=========================================
    ‘ Insert Data Fields
    ‘=========================================

    With ActiveSheet.PivotTables(“PivotTable”).PivotFields(“Medical_Manager__”)
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    End With

    End Sub

    • Scott Gardner 4 Sep, 18 at 3:43 pm - Reply

      I am having the same issue. The new tab is created but none of the data is pulled in.

  20. harish 12 Dec, 17 at 9:32 pm - Reply

    I need to add a filter to the vba code for pivots. how do i do that?

  21. Venky Venkatesh 11 Nov, 17 at 12:08 am - Reply
    • SirKen 2 Feb, 18 at 9:45 pm - Reply

      I have the same error as Venky just above. I do not see any help on correcting this. It is now 2018 and the error seems to still be there. BTW, I used file sent from your site, with its embedded code. Help please
      SirKen

  22. Riaz Dadabhay 9 Nov, 17 at 9:27 am - Reply

    I’ve been trying to create a Pivot Table using VB for the last while, and your code and explanation is by far the best that i’ve come across, it’s absolutely brilliant

    Thank you

    • Puneet Gogia 10 Nov, 17 at 5:13 am - Reply

      I’m glad to hear this.

  23. Ashley 3 Nov, 17 at 1:30 pm - Reply

    I used your code and am trying to change it to create a pivot table with my data. I want it to look like the nice table with Poly ID, Tile Num, Design Feat, etc. in the photo, but I keep getting this instead (see photo). I’m not sure what I’m doing wrong. Can you help please?

    https://uploads.disquscdn.com/images/ec60a316a76431b678f3d60014e28be5e7d036db885405d0bd7930934dc142b2.jpg https://uploads.disquscdn.com/images/0bc1224a2e71cb7fd8911ee5b375dd98c199cef11e9e55ef5f9b6f8d23ff4cee.jpg

    • Puneet Gogia 4 Nov, 17 at 3:49 am - Reply

      Can I have your file? puneet[@]gogia[.]me

  24. Erick Nishimoto 29 Oct, 17 at 6:36 am - Reply

    Thx from Brazil!

    • Puneet Gogia 29 Oct, 17 at 2:20 pm - Reply

      You’re welcome

  25. Neha 28 Oct, 17 at 10:33 pm - Reply

    Hi I used this code and it was making pivots then I made some changes to other codes from then even after inserting this code multiple times it is not creating a pivot can you please help

    • Puneet Gogia 29 Oct, 17 at 2:20 pm - Reply

      what changes you have made?

      • Suresh 9 Nov, 17 at 12:27 pm - Reply

        I just changed the rowfield, colfield and datafield to match my col headers. ofcourse i change the sheet name to “Data” too. i creates a new sheet but just stops there dont create a blank pivot.

        • Puneet Gogia 10 Nov, 17 at 5:13 am - Reply

          It would be great if you share your file with me.

          • Suresh 11 Nov, 17 at 4:58 pm

            Sub InsertPivotTable()

            ‘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

            ‘Insert a New Blank Worksheet
            On Error Resume Next
            Application.DisplayAlerts = False
            Worksheets(“PivotTable”).Delete
            Set PSheet = ActiveWorkbook.Worksheets.Add
            PSheet.Name = “PivotTable”
            Set DSheet = Worksheets(“Page 1″)

            ‘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)

            ‘Define Pivot Cache
            Set Pcache = ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:=PRange). _
            CreatePivotTable(tabledestination:=PSheet.Cells(2, 2), TableName:=”SalesPivotTable”)

            ‘Insert Blank Pivot Table
            Set PTable = Pcache.CreatePivotTable _
            (tabledestination:=PSheet.Cells(1, 1), TableName:=”SalesPivotTable”)

            ‘Insert Row Fields
            With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“Name”)
            .Orientation = xlRowField
            .Position = 1
            End With

            ‘Insert Column Fields
            With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“Priority”)
            .Orientation = xlColumnField
            .Position = 1
            End With

            ‘Insert Data Field
            With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“Number”)
            .Orientation = xlDataField
            .Position = 1
            .Function = xlCount
            .NumberFormat = “#,##0”
            .Name = “Revenue ”
            End With

            ‘Format Pivot Table
            ActiveSheet.PivotTables(“SalesPivotTable”).ShowTableStyleRowStripes = True
            ActiveSheet.PivotTables(“SalesPivotTable”).TableStyle2 = “PivotStyleMedium9”

            End Sub

  26. John Lu 23 Oct, 17 at 2:06 pm - Reply

    https://uploads.disquscdn.com/images/10d5217241229bc5b2fc258f1ee6c8b6a356753998086f930daa740704950b2b.png
    Hi I need help with filtering out 0 values. Can you give me some directions to it? I did the grouping but now want to know if there is a way to filter out 0 values. Thanks,

    • Puneet Gogia 26 Oct, 17 at 10:18 am - Reply

      Can I have a snapshot of your code?

      • John Lu 26 Oct, 17 at 1:46 pm - Reply

        Thanks, I used pf2.ClearAllFilters
        pf2.PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=pf2Values, Value1:=0 to filter out 0 values.

        • Puneet Gogia 29 Oct, 17 at 2:20 pm - Reply

          let me do this for you.

  27. Maria Katrina Sedano 20 Oct, 17 at 8:38 am - Reply

    hi sir, i tried to copy paste the code but the output is this, the data field is empty. https://uploads.disquscdn.com/images/91de66ca41bfb873e0567a19c3a80a319f8abfc78235348381588d56befe21da.png

    thanks for the help.

    btw, its a good post i have been looking for sampla code for creating a pivot.

  28. Diksha Jamnani 10 Oct, 17 at 9:33 am - Reply

    hey, the code really helped 🙂
    Do u have any code tht explains the xlPageField

  29. G 3 Oct, 17 at 3:23 am - Reply

    Thank you for explaining this very well! I was able to understand and use your sample code in my project. Have a great day, Puneet! 😀

    • Puneet Gogia 3 Oct, 17 at 11:51 am - Reply

      I’m glad you like it.

  30. Divyesh 28 Sep, 17 at 6:26 am - Reply

    Doesnt Work for rows > 65000. I dont know whats the issue..!!??

  31. Joseph Sauerland 12 Sep, 17 at 7:25 pm - Reply

    Copy pasted right into the file YOU provided, and 15+ errors. Are you sure you know what you’re doing?

    • Puneet Gogia 12 Sep, 17 at 7:36 pm - Reply

      Hahaha, thanks for the comment. Can you please share your errors with me? It’s working fine on my side.

      • Damian Mazurek 25 Oct, 17 at 3:00 pm - Reply

        Hi, a used this code but nothing happened. It dont create any pivot table in my excel. What could cause that? 🙂 any solutions ?

        • Puneet Gogia 26 Oct, 17 at 10:17 am - Reply

          You need to edit code as per your data as I have mentioned in the post.

  32. Kate 28 Aug, 17 at 7:36 pm - Reply

    Hi!
    I have one problem 🙂 I am recording my macro(doing pivot) as I don’t know Vba. So the problem is that after I run macro the pivot comes out without lines, only total number(one line).
    Do you have any ideas what it can be ?

    • Puneet Gogia 29 Aug, 17 at 2:13 am - Reply

      Can’t say this way. You need to share your file with me.

  33. Vongsi Loryongpao 27 Aug, 17 at 2:33 am - Reply

    27-08-2017, Good read and useful post … Thanks for sharing …

    • Puneet Gogia 9 Sep, 17 at 3:24 pm - Reply

      Thanks for your words.

  34. Carmina 12 Jul, 17 at 3:50 pm - Reply

    Hello, thanks for sharing it! I have downloaded your example and it works just fine, but when I try to use it with one of my tables I get the following error: “Run time error ’13’: Type Mismatch’. when running this piece of code:
    ‘Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
    TableName:=”SalesPivotTable”)

    I’ve already searched for clues all over the internet, but nothing seems to explain whats going on here 🙁

    • Puneet Gogia 12 Jul, 17 at 4:05 pm - Reply

      Please share your file with me ==> puneet@gogia.me. If possible. Other wise share a snapshot for of your raw data.

      • Jatinder Kumar 13 Jul, 17 at 11:00 am - Reply

        hi puneet already shared the file with u on ur mail id

        • Puneet Gogia 14 Jul, 17 at 1:50 pm - Reply

          Sorry, Jatinder didn’t get. Please share again.

          • Zackery Brady 25 Jul, 17 at 6:14 pm

            I get the same error on that part of the code. One of the variable types must be incorrect… I am unable to figure out where the mismatch is…

          • Puneet Gogia 25 Jul, 17 at 9:28 pm

            @zackery_brady:disqus please share a snapshot of your macro code which your are using.

      • achyuth sai 21 Nov, 17 at 6:10 am - Reply

        Hi even i get the same error what to do with it?
        Sub Vip()

        ‘ Vip Macro


        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

        Windows(“open.xlsx”).Activate
        Sheets(“INC”).Select
        Sheets.Add before:=ActiveSheet
        ActiveSheet.Name = “Last Modified Summary”
        Set PSheet = Worksheets(“Last Modified Summary”)
        Set DSheet = Worksheets(“INC”)

        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)

        Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange).
        CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
        TableName:=”Last M Summary”)

        Set PTable = PCache.CreatePivotTable _
        (TableDestination:=PSheet.Cells(6, 1) _
        , TableName:=”Last M Summary”)

        ActiveSheet.PivotTables(“Last M Summary”).AddDataField ActiveSheet.PivotTables( _
        “Last M Summary”).PivotFields(“Incident ID”), “Sum of Incident ID”, xlSum
        With ActiveSheet.PivotTables(“Last M Summary”).PivotFields(“Sum of Incident ID”)
        .Caption = “Count of Incident ID”
        .Function = xlCount
        End With

        With ActiveSheet.PivotTables(“Last M Summary”).PivotFields(“Owned By Team”)
        .Orientation = xlRowField
        .Position = 1
        End With

        With ActiveSheet.PivotTables(“Last M Summary”).PivotFields(“Last Modified Bucket”)
        .Orientation = xlColumnField
        .Position = 1
        End With
        End Sub

    • piyush singla 5 Oct, 17 at 9:40 pm - Reply

      Amazing blog !! i learned a lot.. I am facing this same error on the same line of the code.. Do we know what the solution ?

      • Puneet Gogia 6 Oct, 17 at 2:51 am - Reply

        Share your snap shot.

        • Alex 19 Sep, 18 at 7:34 pm - Reply

          Did you ever find a solution for this? I am having the same error

  35. Miklos Kallo 31 May, 17 at 4:53 pm - Reply

    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

  36. vikas joshi 18 May, 17 at 6:21 pm - Reply

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

  37. Em 17 May, 17 at 8:41 pm - Reply

    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 17 May, 17 at 9:31 pm - Reply

      I’m so glad you liked it.

  38. FuriousDK 26 Apr, 17 at 9:14 am - Reply

    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 1 May, 17 at 8:38 am - Reply

      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.

  39. Jonathan Grunwaldt 16 Feb, 17 at 11:25 pm - Reply

    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

  40. K 13 Feb, 17 at 3:38 pm - Reply

    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 17 Feb, 17 at 7:43 am - Reply

      You have to change data source to external.

      • Shri 15 Aug, 17 at 8:48 am - Reply

        Hi Puneet,

        Creating pivot table for external data source, can you please provide sample code .?

  41. carcanken 5 Dec, 16 at 9:17 pm - Reply

    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 17 Feb, 17 at 7:45 am - Reply

      Please elaborate.

  42. Abhinav 3 Nov, 16 at 3:19 pm - Reply

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

  43. Samir Kumar Naik 30 Oct, 16 at 5:38 pm - Reply

    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 7 Nov, 16 at 7:33 am - Reply

      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

  44. Jessica 27 Oct, 16 at 6:40 pm - Reply

    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 28 Oct, 16 at 5:17 am - Reply

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

      • Ryuzaki 23 Mar, 17 at 10:25 am - Reply

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

        Best of luck

  45. Jessica 27 Oct, 16 at 6:33 pm - Reply

    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

  46. Jessica 27 Oct, 16 at 6:08 pm - Reply

    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?

  47. jose luis 5 Oct, 16 at 1:22 pm - Reply

    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 5 Oct, 16 at 1:50 pm - Reply

      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.

Leave A Comment