PIVOT USING VBA
Majid Raza Hashmi
PROCESS
Split the process of creating a Pivot Table into below given steps
Declare Variables
Insert a New Worksheet
Define Data Range
Create Pivot Cache
Insert a Blank Pivot Table
Insert Row & Column Fields
Insert Data Fields
FormatPivot Table
STEP ONE - DECLARE VARIABLES
Before you start to create a pivot table with VBA, you have to declare some variables that you can
use in your code.
'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
STEP TWO - INSERT A NEW WORKSHEET
Before I create a new pivot table I have to insert a new worksheet for myPivot Table.
'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
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")
STEP THREE - DEFINE DATA RANGE
Now define data range for which you want to use for your Pivot Table.
'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)
In abovecode, LastRow & LastCol will define last row & column of the data
range. And then I have named that data range asPRange. Doing this has a
benefit thatyou dont have to change data sourceevery timewhile
creating the pivot table.
STEP FOUR - CREATE PIVOT CACHE
Before you create aPivot Table,youneed to create pivot cache for that.
In excel 2000 & above you have tocreate a pivot cache to define your data
source.
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")
STEP FIVE INSERT A BLANK PIVOT TABLE
Once you create a pivot cache, next step is to insert a pivot table.
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable_
(TableDestination:=PSheet.Cells(1, 1),
TableName:="SalesPivotTable")
By using above code, yoy can create a blank pivot table & use
pivot cache as a data source.
STEP SIX - INSERT ROW & COLUMN FIELDS
Once you create a blank pivot table, you have toinsert row & column fields
in it.
'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
STEP SEVEN INSERT DATA
Now you need to insert a datafield into the pivot table.
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With
STEP EIGHT - FORMAT PIVOT TABLE
Now, give a final touch of formatting.
'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes =
True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 =
"PivotStyleMedium9"
