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.
It worked perfectly. Thanks for this good instructions.
Ok, first of all, thanks sooo much for what this code!
I was trying to figure out how to automate a Pivot table for a long time.
Now, I know it’s not common/good practice or whatever to have several pivot tables in the same worksheet… but that’s something I want to do. There’s about 8 pivot tables I want in the same worksheet will all be very small and the current report format does have manually made tables all in the same worksheet.
I tried, but I get errors –
Run-time error 1004: The PivotTable field name is not valid. To crete a PivotTable report, you must use date that is organized as a list with labeled columns. If you are changing the name of a PivoTable field, you must type a new name for the field.
I am using a common field in both tables. But the bump seems to be where I define the Pivot Cache a second time… I have been trying to take things out and add things, but I’m lost.
Question, what is the code for inserting Value headers in the Value area?
this code worked for me if placed data with starting point A1, but even after making necessary changes in Prange, when starting point is different, just PivotTable sheet got created without pivot table.
I have been trying to create a pivot table automatically using VBA macro for months – no, years! And this afternoon, with your help – I did it. Thank you so much!
More Power to you, Lucille.
Hi,
Using this code I created 2 pivot tables in one sheet and trying to connect 1 slicer with both the tables but in Slicer Connection settings I don’t find option to select another table. Can anybody help me in this?
My codes are:-
Sub Team_Review()
‘Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim pvtFld As PivotField
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Application.ScreenUpdating = False
‘Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(“Team Quality Metrics”).Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = “Team Quality Metrics”
Application.DisplayAlerts = True
Set PSheet = Worksheets(“Team Quality Metrics”)
Set DSheet = Worksheets(“Temp”)
‘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(20, 2), _
TableName:=”TeamQualityMetrics”)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(19, 1), TableName:=”TeamQualityMetrics”)
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:=”TeamQualityMetrics1″)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:=”TeamQualityMetric1″)
ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables(“TeamQualityMetrics1”) _
, “Author”).Slicers.Add ActiveSheet, , “Author”, “Author”, 122.4, 496.2, 144, _
194.25
Hi Puneet,
I have prepared the Pivot Table through your syntax of vba given, but I thing I need to know that there is one report filter at the top of Pivot where I have to select specific pivot items. Can you please describe the Syntax select Items from Report filter?
Reagards,
Naveen Pathak
what will be the code if we want to create pivot in the same sheet, aside the data?
Hi,
For some reason my code only seems to be creating the tab and no creating the pivot table.
I guess it is struggling to get the data from the source but honestly i’m not sure.
My source data is from B4:U904.
Sub CreatePivot()
‘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
‘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”)
‘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(4, 2).Resize(LastRow, LastCol)
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(3, 1), _
TableName:=”PivotTable1″)
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:=”PivotTable1″)
‘Insert Row Fields
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Threshold result”)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Applicable”)
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Frequency”)
.Orientation = xlRowField
.Position = 3
End With
‘Insert Data Field
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Amount”)
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = “#,##0”
.Name = “Count of Metric assignment ID”
End With
‘Format Pivot
TableActiveSheet.PivotTables(“PivotTable1”).ShowTableStyleRowStripes = TrueActiveSheet.PivotTables(“PivotTable1”).TableStyle2 = “PivotStyleMedium9”
End Sub
Any help would be much appreciated! .
Thanks,
Nathan
Pivot cache fix!
Hi,
I was really struggling to get it to work. The solution is actually really simple.
You need to get rid of the destination in PCashe (this code is repeated later on in insert blank pivot table.
Cheers,
Mat
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, “T_GRIR”)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:=”GRIRpt”)
Dear MadMat,
Thank you!!!!!!!!!!!!!!
HI Punnet,
I copied your code but when I use a command button to automatically update the pivot table, it doesn’t work. At first when i click my command button a blank pivot table appears and when i go back to my module where i copied your code then click the run again it appears the data. Why is it happening and everytime i click the run button in my module it adds more and more data that came from my source sheet.
try from the sample file.
Love You Puneet
Hey Puneet, I used this code successfully in a couple of spreadsheet. However when I tried to use this with a spreadsheet where its data set starts from Row 7 (Row 1 to Row 6 are just informative) and having a Grand Total Row in the last Row, it just doesnt work as it should.
I tried using offset function to omit the last row by -1 when defining the data range for LastRow but it still captured the Grand Total Row.
I set PRange as DSheet.Cells(7,1) as well, it still captured blank rows for some reason.
I debugged and narrowed down to the Define Data Range section where things went wrong. Would you help to review?
‘Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Offset(-1).Row
LastCol = DSheet.Cells(7, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(7, 1).Resize(LastRow, LastCol)
Thanks very helpful
Hi,
I used your code above (literally copied and pasted it into the module) and changed the name of the table and I keep getting errors around the Set PCache.
Any advice?
Thank you
Really it’s awesome way to automate Pivot Table and saves a ton of time. I really want to thank you for helping me to run my project. Thanks a Lot.
Is there any method to use index instead of names in data fields?
Hi,
I used the generic code provided and did some edits to fit what I am attempting to do, the only issue is that my pivot table isn’t actually showing up in my new PivotTable worksheet. I am not sure if it is because of something in my data range definition section or in the data field insertion section… I have copied and pasted the sections I feel might be where the issue is arising. I am new to coding and need some help please
‘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:=”CategoryBreakdown “)
‘Inserting Data Field
With ActiveSheet.PivotTables(“CategoryBreakdown”).PivotFields(“Amount”)
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = “#,## 0”
.Name = “Category”
End With
Hi Puneet can you help me how to get unique count of amount? please share the code to use disctinctcount function ?
@abhishek Binjola…… For multiple datas use xlconsolidated instead of xldatabase..
Hi Puneet,
I am using your code in my file. In which I need to split data into multiple workbooks and then create the Pivot table on each and every workbook but macro unable to take pivot fields. Could you please help me out from this query.