top of page

Create Pivot Table Using VBA

Sub Create_Pivot()

Dim wb As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
Dim rng As Range
Dim pc As PivotCache
Dim pt As PivotTable

​

Set wb = ThisWorkbook
Set ws = wb.Sheets("Data")
Set rng = ws.Range("A1").CurrentRegion
'Add new sheet where we could create our Pivot Table

Sheets.Add 
ActiveSheet.Name = "MIS"
Set sh = wb.Sheets("MIS")
'This is special setting of pivot table
Set pc = wb.PivotCaches.Create(xlDatabase, rng)
Set pt = sh.PivotTables.Add(pc, sh.Range("A4"), "SalesTable")

'Create Pivot Table
With pt
.PivotFields("Product").Orientation = xlRowField
.PivotFields("Purchase Amount").Orientation = xlDataField
.PivotFields("Country").Orientation = xlPageField
.TableStyle2 = "Pivotstylemedium20"  ' for change pivot style 
.RowAxisLayout xlTabularRow   ' for change pivot table layout style 
End With
 

​

IF you want to change Sum of Purchase Amount in to Percent of Total. use below code after end with of above code 

With pt.PivotFields("Sum of Purchase Amount4")
.Calculation = xlPercentOfTotal
.NumberFormat = "0.0%"
.Caption = "% of Total"
End With

IF you want to Calculate Percent of A item use below code after end with of above code, 
here you can calculate , percent of Total, Percent of count, Percent of Average etc. as you require

With pt.PivotFields("Sum of Purchase Amount6")
.Calculation = xlPercentOf
.BaseField = "Product"
.BaseItem = "Desktop"
.Function = xlCount  
.NumberFormat = "0.0%"
.Caption = "% of Desktop on Count"
End With

​

Orientation = xlrowfield -- for Row field

Orientation = xlcolumnfield -- for column field

Orientation = xldatafield -- for Value field

Orientation = xlpagefield -- for filter field

Use Below code with PT

RowAxisLayout   xlCompactRow :- For Compact Layout
RowAxisLayout xlOutlineRow :- Outline Row Layout
.RowAxisLayout xlTabularRow  :- Tabular Row Layout

Eg of change Function 

With pt.PivotFields("Sum of Purchase Amount2")
.Function = xlCount
.Caption = "Count "
End With
 

Function :- 
xlAverage :-   Average.
xlCount :-   Count.
xlCountNums:-    Count numerical values only.
xlDistinctCount :-    Count using Distinct Count analysis.
xlMax :-   Maximum.
xlMin :-   Minimum.
xlProduct :-    Multiply.
xlStDev  :-  Standard deviation, based on a sample.
xlStDevP :-   Standard deviation, based on the whole population.
xlSum  :-   Sum.
xlUnknown  :-   No subtotal function specified.
xlVar :-   Variation, based on a sample.
xlVarP :-   Variation, based on the whole population.

xlDifferenceFrom :-     The difference from the value of the Base item in the Base field.
xlIndex :-     Data calculated as ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total)).
xlNoAdditionalCalculation :-     No calculation.
xlPercentDifferenceFrom :-     Percentage difference from the value of the Base item in the Base field.
xlPercentOf :-     Percentage of the value of the Base item in the Base field.
xlPercentOfColumn :-     Percentage of the total for the column or series.
xlPercentOfParent :-     Percentage of the total of the specified parent Base field.
xlPercentOfParentColumn :-     Percentage of the total of the parent column.
xlPercentOfParentRow :-     Percentage of the total of the parent row.
xlPercentOfRow :-     Percentage of the total for the row or category.
xlPercentOfTotal :-     Percentage of the grand total of all the data or data points in the report.
xlPercentRunningTotal :-     Percentage of the running total of the specified Base field.
xlRankAscending :-     Rank smallest to largest.
xlRankDecending :-     Rank largest to smallest.
xlRunningTotal :-     Data for successive items in the Base field as a running total.

Select pivot select 
PT..PivotSelect "", xlDataAndLabel, True
 

For Copy selected Pivot Table

selection.copy


For move Pivot Table
PT.Location = "MIS!$E$10" (location where you want to move)

Create Calculated Field
CalculatedFields.Add "Avg Amount", _
        "=AVERAGE('Purchase Amount' )", True

For Clear All filter on Filter Page
PT.PivotFields("Country").ClearAllFilters
 

for Enable or disable multiple select
pt.PivotFields("Country"). EnableMultiplePageItems = False
(false for disable, True for enable)
 

for All in Page Field 
.PivotFields("Country").CurrentPage = "(All)"


For hide and show pivot row field 
.With pt.PivotFields("Country")
        .PivotItems("Austria").Visible = True
        .PivotItems("France").Visible = True
end with
 

​

For hide and show Page filter 
with pt.PivotFields("Country")
        .PivotItems("Denmark").Visible = True
        .PivotItems("Finland").Visible = True
        .PivotItems("France").Visible = True
        .PivotItems("Germany").Visible = True
        .PivotItems("India").Visible = True 
end with

​

 

Create Pivot Table Using VBA:- Use All Code

Sub Create_Pivot()
Dim wb As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
Dim rng As Range
Dim pc As PivotCache
Dim pt As PivotTable

Set wb = ThisWorkbook
Set ws = wb.Sheets("Data")
Set rng = ws.Range("A1").CurrentRegion

Application.DisplayAlerts = False
wb.Sheets("MIS").Delete
Sheets.Add
ActiveSheet.Name = "MIS"
Set sh = wb.Sheets("MIS")
Set pc = wb.PivotCaches.Create(xlDatabase, rng)
Set pt = sh.PivotTables.Add(pc, sh.Range("A4"), "SalesTable")

With pt
.PivotFields("Product").Orientation = xlRowField
.PivotFields("Purchase Amount").Orientation = xlDataField
.PivotFields("Purchase Amount").Orientation = xlDataField
.PivotFields("Purchase Amount").Orientation = xlDataField
.PivotFields("Purchase Amount").Orientation = xlDataField
.PivotFields("Purchase Amount").Orientation = xlDataField
.PivotFields("Purchase Amount").Orientation = xlDataField

.PivotFields("Country").Orientation = xlPageField
.TableStyle2 = "Pivotstylemedium20"
.RowAxisLayout xlTabularRow
End With


pt.PivotFields("Sum of Purchase Amount").Caption = "Amount"
With pt.PivotFields("Sum of Purchase Amount2")
.Function = xlCount
.Caption = "Count "
End With

With pt.PivotFields("Sum of Purchase Amount3")
.Function = xlMax
.Caption = "Max Amount"
End With

With pt.PivotFields("Sum of Purchase Amount4")
.Calculation = xlPercentOfTotal
.NumberFormat = "0.0%"
.Caption = "% of Total"
End With

With pt.PivotFields("Sum of Purchase Amount5")
.Calculation = xlPercentOf
.BaseField = "Product"
.BaseItem = "Desktop"
.Function = xlCount
.NumberFormat = "0.0%"
.Caption = "% of Desktop on value"
End With


With pt.PivotFields("Sum of Purchase Amount6")
.Calculation = xlPercentOf
.BaseField = "Product"
.BaseItem = "Desktop"
.Function = xlCount
.NumberFormat = "0.0%"
.Caption = "% of Desktop on Count"
End With


End Sub

Slicer & Timeline

      Add Slicer - Table A 

Suppose we have created a Pivot Table and that Name is SalesTable, and other Name ais FinanceTable, and Pivot Table created in the same workbook on that we are working, and sheet name is MIS,

Dim ws As Worksheet
Dim pt As PivotTable
Dim sli As SlicerCache
Dim wb As Workbook
Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets("MIS")
Set pts = ws.PivotTables("SalesTable")
Set ptf = ws.PivotTables("FinanceTable")

Suppose we have create structure of Table A 
now we want to create a Slicer 
create a Slicer of Country from Sales Table
Set sliS = wb.SlicerCaches.Add2(pt, "Country")

sli.Slicers.Add ws, , "Country", "Country", Range("i4").Top, Range("i4").Left
Create a Timelime or Timeslicer from Sales Pivot Table

Set sliST = wb.SlicerCaches.Add2(pt, "Data", , xlTimeline)
sliST.Slicers.Add ws, , "Date", "Date", Range("K1").Top, Range("K1").Left

delete Slicer which name is Country
ws.Shapes.Range(Array("Country")).Delete

Change Slicer Style which name is Country 
wb.SlicerCaches("Slicer_country").Slicers("Country").Style = "SlicerStylelight3"
 

Remove Slicer Connection
wb.SlicerCaches("Slicer_Country").PivotTables.RemovePivotTable (pt)

Add  Slicer Connection
wb.SlicerCaches("Slicer_Country").PivotTables.AddPivotTable (pt)

bottom of page