Before I hand over this guide to you and you start using VBA to create a pivot table, let me confess something. I learned to use VBA a decade ago.
The first time I wrote a macro code to create a pivot table, it was a failure. Since then, I have learned more from my bad coding than from the codes that work.
Today, I will show you a simple way to automate your pivot tables using a macro code.
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 the same, just executed using code. In this guide, I’ll show you each step and explain how to write code for it.
Just look at the example below, where you can run this macro code with a button, and it returns a new pivot table in a new worksheet in a flash.
Without any further ado, let’s get started writing our macro code to create a pivot table.
[FULL CODE] Use VBA to Create a Pivot Table in Excel – Macro to Copy-Paste
Sub InsertPivotTable()
'Macro By ExcelChamps.com
'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
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sales_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)
'Insert Pivot Table
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Cells(2, 2), TableName:="SalesPivotTable")
'Insert Row Fields
With PTable.PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Salesperson")
.Orientation = xlRowField
.Position = 2
End With
'Insert Column Fields
With PTable.PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue"
End With
'Format Pivot Table
PTable.ShowTableStyleRowStripes = True
PTable.TableStyle2 = "PivotStyleMedium9"
End SubGet the Sample File (EC0010)
The Simple 8 Steps to Write a Macro Code in VBA to Create a Pivot Table in Excel
I have split the entire process into 8 simple steps for your convenience. After following these steps, you will be able to automate all your pivot tables.
1. Declare Variables
The first step is to declare the variables 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 LongHere’s what each line does:
- Dim PSheet As Worksheet: This line declares a Workbook object variable named PSheet, representing a worksheet where the pivot table will be created.
- Dim DSheet As Worksheet: This line declares another Workbook object variable named DSheet, which will represent the worksheet that contains the source data.
- Dim PCache As PivotCache: This line declares a PivotCache object variable named PCach, used to store the data for the pivot table.
- Dim PTable As PivotTable: This line declares a PivotTable object variable namedPTable`, which will represent the pivot table itself.
- Dim PRange As Range: This line declares a Range object variable named PRange, representing the data range the pivot table will analyze.
- Dim LastRow As Long: This line declares a Long variable named LastRow, which will store the row number of the last row in the data set.
- Dim LastCol As Long: This line declares another Long variable named LastCol, which will be used to store the column number of the last column in the data set.
2. Insert a New Worksheet
Before creating a pivot table, Excel inserts a blank sheet and then creates a new pivot table there.
And, the below code will do the same for you.
'Declare Variables
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")- On Error Resume Next: This line prevents the code from stopping if it encounters an error. Instead, it ignores the error and moves to the next line of code.
- Application.DisplayAlerts = False: This line turns off alerts and warnings that Excel might show while running the code.
- Worksheets(“PivotTable”).Delete: This line deletes the worksheet named “PivotTable” if it exists already.
- Sheets.Add Before:=ActiveSheet: This line adds a new sheet before the current active sheet.
- ActiveSheet.Name = “PivotTable”: This line renames the newly created sheet to “PivotTable”.
- Application.DisplayAlerts = True: This line turns the alerts back on after the operations are done.
- Set PSheet = Worksheets(“PivotTable”): This line creates a reference to the “PivotTable” worksheet and assigns this reference to the variable PSheet.
- Set DSheet = Worksheets(“Data”): This line creates a reference to the “Data” worksheet and assigns this reference to the variable DSheet.
After inserting a new worksheet, this code will set the value of the PSheet variable to the pivot table worksheet and DSheet to the source data worksheet.
Note – Make sure to change the name of the worksheets in the code to the names that you have in your data.
3. Define Data Range
Now, the next thing is to define the data range from the source worksheet. Here, you need to take care of one thing: you can’t specify a fixed source range.
You need a code that can identify the entire data from the source sheet. 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)- LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row: This line finds the last row of the first column (column 1) with data. Rows.Count gives the total number of rows in the worksheet, and End(xlUp) moves up from the bottom until it finds a cell with data.
- LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column: This line finds the last column of the first row (row 1) with data. Columns.Count gives the total number of columns in the worksheet, and End(xlToLeft) moves to the left from the far right of the worksheet until it finds a cell with data.
- Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol): This line sets a range that starts from the cell in the first row and first column (Cell 1, 1) and resizes it to include all cells until the last row with data and the last column with data. PRange now refers to the range of cells that contains data in the worksheet.
Note – You don’t need to change the data source every time while creating the pivot table.
4. Create a 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 code for this.
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=PRange)- TableName:=”SalesPivotTable”: This parameter in the
CreatePivotTablemethod sets the name of the pivot table being created. In this case, the pivot table will be named “SalesPivotTable”. - Set PCache = ActiveWorkbook.PivotCaches.Create: This line of code is initializing the creation of a cache for the pivot table. A pivot cache is a particular type of data structure that Excel uses to store a snapshot of the source data for a pivot table. The
ActiveWorkbookobject refers to the active workbook in the Excel application. - SourceType:=xlDatabase: This line in the
Createmethod specifies the type of data source used for the pivot table. ThexlDatabasevalue indicates that the source data is coming from a database, which in Excel could be a range of cells in a spreadsheet structured like a database. - SourceData:=PRange: This line in the Create method specifies the data used for the pivot table. In this case, the
PRangerefers to a named range in the workbook that contains the source data. - .CreatePivotTable: This method is called on the pivot cache object (
PCache) and creates the pivot table from the data stored in it. - TableDestination:=PSheet.Cells(2, 2): This parameter in the CreatePivotTable method specifies where the upper-left cell of the pivot table will be added in the worksheet. The PSheet.Cells(2,2) value represents the cell in the second row and second column of the sheet referred to by PSheet.
5. Insert a Blank Pivot Table
After the pivot cache, the 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 rows.
This code will do the same:
'Insert Pivot Table
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Cells(2, 2), 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, the 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 months in the row field and zones in the column field.
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 WithIn this code, you have mentioned year and month as two fields. Now, if you look at the code, you’ll find that a position number is also there.
This position number defines the sequence of fields.
Whenever you need to add more than one field (Row or Column), specify their position.
You can change fields by editing their name from the code.
7. Insert Values
The main thing is to define the value field in your pivot table.
The code for defining values differs from defining rows and columns because we must define the formatting of numbers, positions, and functions here.
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End WithYou can add the amount as the value field with the above code. And this code will format values as a number with a (,) separator.
We use xlsum to sum values, but you can also use xlcount and other functions.
8. Format Pivot Table
You need to use a code to format your pivot table. There is a default formatting in a pivot table, but you can change that formatting.
With VBA, you can define formatting styles within the code.
Code is:
'Format Pivot
TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"The above code will apply row strips and the “Pivot Style Medium 9” style, but you can also use another style from this link.
Finally, your code is ready to use.
[FULL CODE] VBA Code to Create Multiple Pivot Tables from the Same Data Source.
The code below creates eight pivot tables on a new worksheet from the same data source.
Sub Insert_Multiple_Pivot_Tables()
'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 pvt As PivotTable
'Delete and Add Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sales_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)
'Create Pivot Cache Once
'Create a Pivot Cache from the source data range only once
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=PRange)
'Pivot 1 – Region-Wise Total Sales
'Create Pivot Table for Region-Wise Total Sales
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("A1"), _
TableName:="Pivot1_RegionSales")
With PTable.PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot1_RegionSales"
End With
'Pivot 2 – Product-Wise Total Sales
'Create Pivot Table for Product-Wise Total Sales
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("A8"), _
TableName:="Pivot2_ProductSales")
With PTable.PivotFields("Product")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot2_ProductSales"
End With
'Pivot 3 – Payment Mode Wise Total Sales
'Create Pivot Table for Payment Mode-Wise Total Sales
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("D1"), _
TableName:="Pivot3_PaymentSales")
With PTable.PivotFields("Payment Mode")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot3_PaymentSales"
End With
'Pivot 4 – Delivery Status Wise Units Sold
'Create Pivot Table for Delivery Status-Wise Units Sold
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("D9"), _
TableName:="Pivot4_DeliveryUnits")
With PTable.PivotFields("Delivery Status")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Units Sold")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot4_DeliveryUnits"
End With
'Pivot 5 – Customer Type Wise Total Sales
'Create Pivot Table for Customer Type-Wise Total Sales
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("G1"), _
TableName:="Pivot5_CustomerSales")
With PTable.PivotFields("Customer Type")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot5_CustomerSales"
End With
'Pivot 6 – Order Priority Wise Units Sold
'Create Pivot Table for Order Priority-Wise Units Sold
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("A19"), _
TableName:="Pivot6_PriorityUnits")
With PTable.PivotFields("Order Priority")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Units Sold")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot6_PriorityUnits"
End With
'Pivot 7 – Warranty Wise Units Sold
'Create Pivot Table for Warranty-Wise Units Sold
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("D16"), _
TableName:="Pivot7_WarrantyUnits")
With PTable.PivotFields("Warranty")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Units Sold")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot7_WarrantyUnits"
End With
'Pivot 8 – Return Eligibility Wise Units Sold
'Create Pivot Table for Return Eligibility-Wise Units Sold
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Range("G8"), _
TableName:="Pivot8_ReturnUnits")
With PTable.PivotFields("Return Eligibility")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Units Sold")
.Orientation = xlDataField
.Function = xlSum
.Name = "Pivot8_ReturnUnits"
End With
'Loop through each Pivot Table on the PivotTable worksheet to apply formatting
For Each pvt In PSheet.PivotTables
pvt.ShowTableStyleRowStripes = True
pvt.TableStyle2 = "PivotStyleMedium9"
Next pvt
End Sub
Just like the code that we have used to create a single pivot table, this code inserts a new worksheet with the name Pivot Table and then creates 8 different pivot tables on that sheet.
Then it goes to the SalesData worksheet and defines the source data that it will use to create a pivot cache.
As you have the same data source, instead of creating a pivot cache with each pivot table, this code creates a single pivot cache and then use it for creating each of the pivot tables.
And then insert eight pivot tables one by one using a different location within the worksheet so that none of the pivot table overlaps the other.
[FULL CODE] Pivot Table on the Existing Worksheet
Sub Insert_Pivot_Tables_Existing_Sheet()
Dim wsName As String
Dim TargetSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long, LastCol As Long
Dim LastUsedRow As Long
Dim InsertCell As Range
'Ask user for the sheet name
wsName = InputBox( _
Prompt:="Enter sheet name to insert a pivot table.", _
Title:="Target Sheet")
'Check if sheet exists
On Error Resume Next
Set TargetSheet = ThisWorkbook.Worksheets(wsName)
On Error GoTo 0
If TargetSheet Is Nothing Then
MsgBox "Sheet '" & wsName & "' does not exist.", vbExclamation
Exit Sub
End If
'Set source data sheet
Set DSheet = Worksheets("Sales_Data")
'Define source 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)
'Check if the sheet has any content
Dim lastCell As Range
Set lastCell = TargetSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If lastCell Is Nothing Then
'Sheet is blank, so insert at B2
Set InsertCell = TargetSheet.Range("B2")
Else
'Sheet has data, insert pivot 2 rows below the last used row
LastUsedRow = lastCell.Row
Set InsertCell = TargetSheet.Cells(LastUsedRow + 2, 2)
End If
'Create Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)
'Create the Pivot Table at the target cell with a unique name based on current time
Set PTable = PCache.CreatePivotTable( _
TableDestination:=InsertCell, _
TableName:="SalesPivot_" & Format(Now, "hhmmss"))
'Add Fields
With PTable.PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Salesperson")
.Orientation = xlRowField
.Position = 2
End With
With PTable.PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue"
End With
'Apply style
With PTable
.ShowTableStyleRowStripes = True
.TableStyle2 = "PivotStyleMedium9"
End With
MsgBox "Pivot Table inserted in '" & wsName & _
"' sheet at cell " & InsertCell.Address, vbInformation
End SubAdding a Filter along with Creating a Pivot Table
Below are a simple lines of code that you can use while writing a code to create a pivot table that allows you to add a filter to the pivot table.
With PTable.PivotFields("Year")
.Orientation = xlPageField
.Position = 1
End WithHere is the complete code that can create a new pivot table and add a “Year” column as a filter for the pivot table.
Sub InsertPivotTable()
'Macro By ExcelChamps.com
'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
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sales_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)
'Insert Pivot Table
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Cells(2, 2), TableName:="SalesPivotTable")
'Insert Filter Field (Page Field)
With PTable.PivotFields("Location")
.Orientation = xlPageField
.Position = 1
End With
'Insert Row Fields
With PTable.PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With PTable.PivotFields("Salesperson")
.Orientation = xlRowField
.Position = 2
End With
'Insert Column Fields
With PTable.PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With PTable.PivotFields("Total Sales")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue"
End With
'Format Pivot Table
PTable.ShowTableStyleRowStripes = True
PTable.TableStyle2 = "PivotStyleMedium9"
End Sub[SAMPLE FILES] (EC0010)
In the end,
By using this code, we can automate your pivot tables.
And the best part is this is a one-time setup; after that, we just need a click to create a pivot table, and you can save a ton of time.
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 them with you and share this tip with your friends.
VBA is one of the Advanced Excel Skills, and if you are getting started with VBA, make sure to check out the Useful Macro Examples and VBA Codes.
hey, the code really helped 🙂
Do u have any code tht explains the xlPageField
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! 😀
I’m glad you like it.
Doesnt Work for rows > 65000. I dont know whats the issue..!!??
Can I have your file?
It works for me. Can I have the snapshot of error you have? Refer this thread ==> https://excelchamps.com/vba/vba-pivot-table/#comment-3274865608
Copy pasted right into the file YOU provided, and 15+ errors. Are you sure you know what you’re doing?
Hahaha, thanks for the comment. Can you please share your errors with me? It’s working fine on my side.
Hi, a used this code but nothing happened. It dont create any pivot table in my excel. What could cause that? 🙂 any solutions ?
You need to edit code as per your data as I have mentioned in the post.
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 ?
Can’t say this way. You need to share your file with me.
27-08-2017, Good read and useful post … Thanks for sharing …
Thanks for your words.
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 🙁
Please share your file with me ==> puneet@gogia.me. If possible. Other wise share a snapshot for of your raw data.
hi puneet already shared the file with u on ur mail id
Sorry, Jatinder didn’t get. Please share again.
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…
@zackery_brady:disqus please share a snapshot of your macro code which your are using.
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
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 ?
Share your snap shot.
Did you ever find a solution for this? I am having the same error
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
Thanks for your suggestions, I’ve been struggling to get this to work for hours but after reading your comment I finally got it to work.
How will open a exl file from particular location with a only keyword of that file, and save it to other location using vba?
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!
I’m so glad you liked it.
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?
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.
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
Hey Jonathan debug your code & send me a screen shot at puneet@puneetgogia.com.
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
You have to change data source to external.
Hi Puneet,
Creating pivot table for external data source, can you please provide sample code .?
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
Please elaborate.
Excellent Blogpost! Do you also have any such blog for Pivot Chart?
Hey Abhinav, Thanks, I hope this will help.
(https://excelchamps.com/excel-charts/pivot-chart/)
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″)
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
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
I’ll appreciate if you share your file or code.
I think it’s a bit late, but all you have to do is change .Function = xlSum to .Function = xlCount
Best of luck
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
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?
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
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.