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:

 

Comments

Great - worked a treat!

Great - worked a treat!

Question

What can I do if I only wanted to select one of the Activity and make it a seperate tab? For example, I need the "Activity" "Development" "Email" and "Lunch" in seperate tabs through VBA coding. What can I do?Thank you sooo much for your help!Novell

Dynamic creation of pivot table

Thanks a lot.this is really a well formed study notes for pivot table dynamic creation.:)

Agreed...really well done.

Searched a lot of sites until I found this version that actually WORKS! Thanks!!!