Before I hand over this guide to you and you start using VBA to create a pivot table let me confess something. I have learned using VBA just SIX 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. 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 same, just executes using a code. In this guide, I’ll show you each step and explain 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.
Without any further ado, let’s get started to write 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 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
The Simple 8 Steps to Write a Macro Code in VBA to Create a Pivot Table in Excel
For your convenience, I have split the entire process into 8 simple steps. After following these steps you will able to automate your all the pivot tables. Make sure to download this file from here to follow along.
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
Here’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, 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 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 which 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. 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)
- 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 a code for this.
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")
- Set PCache = ActiveWorkbook.PivotCaches.Create: This line of the 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
ActiveWorkbook
object refers to the active workbook in the Excel application. - SourceType:=xlDatabase: This line in the
Create
method specifies the type of data source used for the pivot table. ThexlDatabase
value 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
PRange
refers 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 aded 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.
- TableName:=”SalesPivotTable”: This parameter in the
CreatePivotTable
method sets the name of the pivot table being created. In this case, the pivot table will be named “SalesPivotTable”.
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 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, 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 With
In 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. And you can change fields by editing their name from the code.
7. Insert Data Field
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 With
You 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
Ultimately, you need to use a code to format your pivot table. Typically there is a default formatting in a pivot table, but you can change that formatting.
With VBA, you can define formatting style 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.
Download Sample File
Pivot Table on the Existing Worksheet
The code we have used above creates a pivot table on a new worksheet, but sometimes you need to insert a pivot table in a worksheet already in the workbook.
In the above code (Pivot Table in New Worksheet), in the part where you have written the code to insert a new worksheet and then name it. Please make some tweaks to the code.
Don’t worry; I’ll show you.
You first need to specify the worksheet (already in the workbook) where you want to insert your pivot table.
And for this, you need to use the below code:
Instead of inserting a new worksheet, you must specify the worksheet name to the PSheet variable.
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets(“Data”)
There is a bit more to do. The first code you used deletes the worksheet with the same name (if it exists) before inserting the pivot.
When you insert a pivot table in the existing worksheet, there’s a chance that you already have a pivot there with the same name.
What I’m saying is you need to delete that pivot first.
For this, you need to add the code which should delete the pivot with the same name from the worksheet (if it’s there) before inserting a new one.
Here’s the code which you need to add:
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets(“Data”)
Worksheets("PivotTable").Activate
On Error Resume Next
ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear
Let me tell you what this code does.
First, it simply sets PSheet as the worksheet where you want to insert the pivot table already in your workbook and sets data worksheets as DSheet.
After that, it activates the worksheet and deletes the pivot table “Sales Pivot Table” from it.
Important: If the worksheets’ names in your workbook differ, you can change them from the code. I have highlighted the code where you need to do it.
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 there and Useful Macro Examples and VBA Codes.
- Add or Remove Grand Total in a Pivot Table in Excel
- Add Running Total in a Pivot Table in Excel
- Automatically Update a Pivot Table in Excel
- Add Calculated Field and Item
- Delete a Pivot Table in Excel
- Filter a Pivot Table in Excel
- Add Ranks in Pivot Table in Excel
- Apply Conditional Formatting to a Pivot Table in Excel
- Create Pivot Table using Multiple Files in Excel
- Change Data Source for Pivot Table in Excel
- Count Unique Values in a Pivot Table in Excel
- Pivot Chart in Excel
- Create a Pivot Table from Multiple Worksheets
- Sort a Pivot Table in Excel
- Refresh All Pivot Tables at Once in Excel
- Refresh a Pivot Table
- Pivot Table Timeline in Excel
- Pivot Table Keyboard Shortcuts
- Pivot Table Formatting
- Move a Pivot Table
- Link a Slicer with Multiple Pivot Tables in Excel
- Group Dates in a Pivot Table in Excel
how can I add mulitple pivot tables in the same sheet using the code ??
I try with the same code, Pivot sheet (In the next sheet the new sheet is added) got created.
But the Pivot table in the new sheet is not appearing? How do I debug this?
Can someone help me
screenshot please
puneet-@-gogia-.-me
Can someone help me?
I have some issues in the code below. When running the macro it stops right after creating the new sheet. No error message shows up.
‘ insert pivot table
‘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(“IHOD in the past”).Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = “IHOD in the past”
Application.DisplayAlerts = True
Set PSheet = Worksheets(“IHOD in the past”)
Set DSheet = Worksheets(“DBS 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:=”IHODinthepast”)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:=”IHODinthepast”)
try using it from the sample file.
Works for me in the sample file, but when I tried to use in the actual file – all I got was an empty worksheet.
Check for the fields name and worksheets name in the code.
The reason you aren’t getting any error messages/alerts is because at the beginning of your code, it says “On Error Resume Next” which tells VBA to ignore any errors and to resume the code with the next line following an error. This is necessary when deleting a worksheet that may/may not exist (if the worksheet you’re deleting doesn’t yet exist, it will cause an error and will stop your code). However, after you have deleted the worksheet, you need to say “On Error Goto 0” which tells VBA to handle errors normally when they occur.
the PI suggest the following improvement to your code:
Instead of
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:=”SalesPivotTable”)
it should read
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)
The reason is that you create a type mismatch as PCache is not a pivot table.
Hi. I used this, but it creates a new sheet but doesn’t actually insert in a pivot table….
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
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:=”OutOfPocketSpend”)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:=”OutOfPocketSpend”)
‘Insert Row Fields
With ActiveSheet.PivotTables(“OutOfPocketSpend”).PivotFields(“Sales Order Facility”)
.Orientation = xlRowField
.Position = 1
End With
‘Insert Column Fields
With ActiveSheet.PivotTables(“OutOfPocketSpend”).PivotFields(“Status”)
.Orientation = xlColumnField
.Position = 1
End With
‘Insert Data Field
With ActiveSheet.PivotTables(“OutOfPocketSpend”).PivotFields(“Sales Order Detail Extended Allowance Amount”)
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = “$#,##0”
.Name = “Revenue ”
End With
‘Format Pivot
TableActiveSheet.PivotTables(“OutOfPocketSpend”).ShowTableStyleRowStripes = TrueActiveSheet.PivotTables(“OutOfPocketSpend”).TableStyle2 = “PivotStyleMedium9”
Very informative – will return to your website blogs again
Very very useful, thank you very much.
Really clear and helpful. Thanks!
Hello, I was wondering how you would change this if your values do not start a 1,1. I have been trying but it has not been working.
SUPER EXPLANATION. GREATLY APPRECIATED
This has been extremely helpful in fixing a broken code that I have in a creating a pivot table. The question I have, what is the proper code if the title of your columns change? I am working on a manufacturing capacity outlook file that changes weekly. I need to have my column names in my pivot table change to the upcoming weeks.
So instead of: ActiveSheet.PivotTables(“PivotTable5”).AddDataField ActiveSheet.PivotTables( _
“PivotTable5”).PivotFields(“19-Oct-21”), “Sum of 19-Oct-21”, xlSum
I need: ActiveSheet.PivotTables(“PivotTable5”).AddDataField ActiveSheet.PivotTables( _
“PivotTable5”).PivotFields(E2), “Sum of “E2, xlSum
But I get an error message Expected: end of statement with E2 highlighted after “Sum of”.
I know this is super late, but maybe this will help someone else with a similar question:
VBA doesn’t recognize E2 as a range by itself, so it doesn’t know what you’re referring to. Instead you could write it as:
.PivotFields(Range(“E2”))
When you’re attaching a string (text) to something like a range reference, you must insert an ‘&’ in between so that VBA knows to combine the two types of variables into one. For example:
“Sum of ” & Range(“E2”)
Awesome. It really helps to enhance my knowledge of VBA. Now new challenge – filtering.
Thank you so much! Very helpful!
Dear ExcelChamps,
Your code worked perfectly fine for me.
I was able to create the pivot table using the VBA. However, I have a requirement to get the pivot table in the classical view and also with subtotals removed. Now I am doing this manually after generating the pivot table using your code. Is it possible to convert the pivot table in to classical view and remove subtotals using VBA? Kindly guide me for the same if it is possible.
Thanks in advance.
to help figure out the code, record your actions when changing the pivot table in classical view and subtotals removed. the macro created will contain the code to automate it
For anyone having issues with the Values field, it looks like moving the .PivotFields up to the first line of the With statement makes it work, e.g.:
With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields (“Amount”)
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = “#,##0”
.Name = “Revenue ”
End With
This was very easy to follow, it worked perfectly! THANK YOU!
This worked… Sort of.
I could set the Row and Column of the PT, but not the Data Field when I changed it from Sum to Count. Ended up recording a macro to set the data field and then pasted the code into the module and it now works. See below for your code (with count instead of sum), and the changes I made.
‘Insert Data Field
With ActiveSheet.PivotTables(“409PivotTable”)
‘THIS WAS THE FROM THE ORIGINAL EX. CODE THAT DIDN’T SEEM TO WORK
‘.PivotFields (“total_rec”)
‘.Orientation = xlDataField
‘.Function = xlCount
‘.Caption = “Special Name”
‘.NumberFormat = “#,##0”
‘.Name = “409PivotTable”
‘THIS IS A COPY/PASTE FROM RECORDING A MACRO TO ADD THE VALUES FIELD
ActiveSheet.PivotTables(“409PivotTable”).AddDataField ActiveSheet.PivotTables( _
“409PivotTable”).PivotFields(“assignedownergroup”), “Count of assignedownergroup”, _
xlCount
End With
Dear excelchamps,
I have tried above PivotTable coding, i have below mention 2 queries,
1. it is showing object variable or with block variable not set.
2. I wanted to taken pivot from second row
Please help me to find my resolution.
Thanks in advance…
This is an excellent way of explaining. Great job!
Puneet:
Please allow me to explain what I am trying to do. Part one of my Macro pulls data into an Excel sheet. Part two then takes the data and parses it into a usable form. For part three, I’d like to have VBA automatically create pivot tables. However, I want to incorporate “Distinct Count” into dome of the pivots. From using the macro recorder, I see that ADD2 is used to make a data connection. I have tried to develop a way to have VBA automatically make the connection, but my attempts are not successful. The Connection String seems to be consistent. Thus, this could be easily written into VBA. I have tried to use Range to deal with the objects in ADD2. In other words, I have the appropriate wording in cells and point the VBA to those cells. Yet, I am still not able to get this to work. If need be, I can send you my code. Thanks!
Puneet: This was excellent information. Thank you! The one thing I seem to be struggling with is how to have VBA generate a Pivot Table that incorporates a Data Model. I have been unsuccessful thus far in writing such a macro. Any thoughts on how to do this?
need more words from you on this.
Great post.
one thing i’m struggling with is to convert the pivot into classic view.
please help
i tried adding this part of the script at the end but no change
With ActiveSheet.PivotTables(“Comm”)
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Your step by step tutorial is great, defining a dynamic range for my dataset was a challenge and you simplified it. Thank you, all the best to you.
Great post! Thanks a lot.
Hi,
Thanks for the code.
1. Could you please advise how to create a filter on a column so that only selected data will appear in the pivot table?
2. Could you please show an example of creating a new column based on calculation from the existing table?
Thanks again
Thanks, I managed to create my 1st pivot table using your code. If I want to use the same data source to create 2nd pivot table in different sheet (eg. sheet2), how do I go about it?
this is awesome stuff. Thanks!
Je suis très ravi de trouver les solutions à mes difficultés concernant la création d’un Tableau croisé Dynamique par VBA
Get error 5 when I try to create a pivot table. I have done this in the past but in Office 2016 I can’t get it to work.
Terry, try with the sample file once.
Hi Puneet,
Thanks a lot for this free tutorial on Pivot table by VBA. I am a 65 years old mathematics teacher, who also handles data analysis. Although built in pivot table serves my purpose, I have been curious to create one using VBA.
Thanks a lot once again.
Ashokan
Singapore
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(20, 2), _
TableName:=”TeamQualityMetrics”)
Dear Sir,
On this I am getting a compile error, Method or data member not found.
Can you pls help
Regards
Manoj
I have delete that part of the code and add it like this and it is working for me:
Set PRange = DSheet.Cells(1, 1).Resize(LastRow1, LastCol)
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
PRange, Version:=6).CreatePivotTable TableDestination:= _
“PivotTable!R1C1″, TableName:=”SalesPivotTable”, DefaultVersion:=6
Sheets(“PivotTable”).Select
Cells(1, 1).Select
With ActiveSheet.PivotTables(“SalesPivotTable”)
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = “”
.MergeLabels = False
.NullString = “”
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables(“SalesPivotTable”).PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables(“SalesPivotTable”).RepeatAllLabels xlRepeatLabels
‘Insert Row Fields
With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“Local Legal Entity Code”)
.Orientation = xlRowField
.Position = 1
End With
Please explain 6 or multiple pivot table creation on a single worksheet
Hello. Thank you for the great tutorial.
Can you please explain how I can add the coding to this VBA Project to select the check box “Add this data the the Data Model” located at the bottom of the in the Create Pivot Table Screen?
What is the code to change to show to subtotals, collapse buttons + – and to view in Tabular?
Thanks,
Victor
Hi,
I used your code, and it works great! But my pivot does not need a column field, so I removed the code to add the column field (i.e. my column would just show sum of sales) but now it doesn’t show any values. It just gives me the rows
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.
I am using office 365. Your code is note creating -data field. Even I tried with our example. can u correct for office 365 if an different
‘Insert Data Field
With ActiveSheet.PivotTables(“SalesPivotTable”)
.PivotFields (“Amount”)
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = “#,##0”
.Name = “Revenue ”
End With’Insert Data Field
With ActiveSheet.PivotTables(“SalesPivotTable”)
.PivotFields (“Amount”)
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = “#,##0”
.Name = “Revenue ”
End With
Hi Piotr, i also had the same error. Guess you found the solution by yourself, but maybe for all other guys who come across.
The return value is a Pivottable not a Pivotcache. Change it to:
Set PTable = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:=”SalesPivotTable”)
This is not working still in Excel 2016. It says Run time error type mismatch. Can you please help?
Hi Dinesh,
Just wondering if you have found solution? I got the same error and cannot figure out how to solve it.
Kind regards,
Roy
Hi,
Can we play with Filter Option one by one in Pivot Table,
For example,
Step1: get the data corresponding the seet1.range A2
Step2: get the data corresponding to the sheet1.range A3
likewise.
Pl. help.
Hello
Using Excel 2010. I took this code from one of your examples and modified it to add in page fields
I have these two pieces of code below that put 2 fields into the page area of a pivot table.
I want to choose 2 out of 40 items in OrderID
I want to choose 1 out of 20 items in CustomerID
Is this possible without having to write true vs false code for each item in VBA?
Any alternative code greatly appreciated.
‘Insert Page Fields
With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“OrderID”)
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“CustomerID”)
.Orientation = xlPageField
.Position = 2
End With
It works! Thank you so much!
You’re Welcome 🙂
Hello,
Please help in resolve the error.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“Sheet2!R1C1:R479C6”, Version:=6).CreatePivotTable TableDestination:= _
“Sheet3!R3C1″, TableName:=”PivotTable2”, DefaultVersion:=6
Hi the code works well thank you. I would like to add one more thing to it but can’t seem to figure it out. I want it to filter as per your example, 2014 only. How do I make it just show 2014 in my pivot? Don’t know if you can help but appreciate your post either way.
doesn’t seem to add Amount field to pivot table
Hi. The macro doesn’t work in excel 2010. When i step into (F8) the code to line ( I comment ‘ on error resume next ):
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:=”SalesPivotTable”)
i have:
Run-time error 13 type mismatch.
Can you please help me?
Hi Punnett, this is brilliant, thank you. it wasnt working for me originally as i couldnt just copy and paste my data into a new worksheet – i had to make sure the data was set as a table.
Is there anyway to build the pivot table so that “Dsheet” doesnt have to be a table – just copied and pasted data with the first row to be used as the headers?
Thanks for posting this Puneet, this is exactly what i needed
Is there any way of amending the code so that it will work any worksheet and not just worksheets that are named “Data”?
You just need to change worksheet name in the code.
This is great! Thank you, Puneet!
I’m following up on Matt’s question. Is there any way to amend the code so I can run the macro regardless of the title on the worksheet without having to change the name in the code each time? I work with many data sets on a daily basis. Wondering if this is possible.
Puneet,
I am having trouble setting this up; very similar, yet a bit different. I will have the button on “Paste Data” sheet and it will be pulling the data to create the pivot from “Formatted” sheet.
Also, there is an additional column header. The setup of the pivot table should be as follows:
Filter – Order Type
Columns – Sum Values
Rows – 1. Salesman Name
2. G/L Cat
Values – 1. Sum of Extended Price
2. Sum of Commission Cost
I can email you the code if you’d like. Please advise.
Thanks!
This is Yaakov calling. Thank you very much for your kindness. Very inspiring and interesting site; I learn a lot from it. Keep on with the good work!
By the way trying your code worked just fine except for the ampunt field which did not show up automatically but i had to check it mannually. Reason?
Bye
Hi puneet,
Its awesome! Can i get this to my email!
This code does not work in Excel 2016
how to remove desirable variable from pivot table with vba
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
Thank you so much for this. Saved me a huge amount of time.
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
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?
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.
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 🙂
Thanks for this post! Clear and works
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!
Can I have a mail puneet[@]gogia[.]me ?
Thank you man! You are awesome!
You’re Welcome.
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.
Make sure you are using an Excel table and using its name in source reference. Knock me back if you need further help.
This was amazing. Thank you so much!
I’m so glad you liked it.
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?
Send me the Sample file pls.
ishankacrashed93@outlook.com
Ishanka
Ishanka
ishankacrashed93@outlook.com
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
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
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
I am having the same issue. The new tab is created but none of the data is pulled in.
I need to add a filter to the vba code for pivots. how do i do that?
Hi, I don’t understand Why i am getting error saying https://uploads.disquscdn.com/images/d917f5a00ea9429e9cdc1e80f3fa1f494f194cccef22120d6d48a082bab2e3f4.jpg
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
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
I’m glad to hear this.
Any possibility you may guide us to use the code in Excel 2016. It just does not work.
Thanks.
What isn’t working for you?
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
Can I have your file? puneet[@]gogia[.]me
Thx from Brazil!
You’re welcome
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
what changes you have made?
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.
It would be great if you share your file with me.
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
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,
Can I have a snapshot of your code?
Thanks, I used pf2.ClearAllFilters
pf2.PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=pf2Values, Value1:=0 to filter out 0 values.
let me do this for you.
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.
Can you please share your file with me?
Can I have a snapshot of your code or file?
https://uploads.disquscdn.com/images/53172da9292f7402553dd7e7353d8e03297d5be2e6933d3a0cab10867d3913cc.png
here is the snapshot of the code.
correct the second last section of the code where three lines are written as comment..
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.