Pivot Table Creation Using Excel VBA
To help automate data processing using Excel VBA, one of the most useful tools to use is a PivotTable. But you may be thinking that a PivotTable is so complicated to do by hand, that it must be very hard to do in VBA. I'm here to show you that it really isn't that hard. I often will use a PivotTable created using VBA to create a view on the data that allows me to use GetPivotData() calls from within the excel spreadsheet to pull summaries out of the PivotTable like I need.
Here is the data that is located on Sheet 1 that I will make the pivot table out of.
To be able to see the summary of hours by the different activities, I would solve this problem by creating a pivot table in excel that would have a row containing Activity, and the data would be a sum of Hours.
To do this in VB, I will take the data on Sheet1, and create a pivot table on Sheet2, using this code.
Sub MakePivotTable() Dim pt As PivotTable Dim strField As String Dim WSD As Worksheet Set WSD = Worksheets("Sheet1") Dim PTOutput As Worksheet Set PTOutput = Worksheets("Sheet2") Dim PTCache As PivotCache Dim PRange As Range ' Find the last row with data Dim finalRow As Long finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row ' Find the last column with data Dim finalCol As Long finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column ' Find the range of the data Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol) Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange) ' Create the pivot table Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _ TableName:="SamplePivot") ' Define the layout of the pivot table ' Set update to manual to avoid recomputation while laying out pt.ManualUpdate = True ' Set up the row fields pt.AddFields RowFields:=Array( _ "Activity") ' Set up the data fields With pt.PivotFields("Hours") .Orientation = xlDataField .Function = xlSum .Position = 1 End With ' Now calc the pivot table pt.ManualUpdate = False End Sub
The PivotTable that this creates is: