Pivot table in MS excel is a great tool to summarize data and save lot of analysis time. sometime we need to automate even pivot table by VBA.

For an example – You have a large data and you want to create so many reports sheets doing filtering and summarizing it. pivot table can make it faster and with VBA you can make it easier too.

This tutorial will help you understand how pivot tables can be automated in VBA. have a look at the screenshot shown below. “Refresh Pivot Table” button will refresh this table by using the data in the data table left of pivot table. this program is even making this pivot table use the data dynamically. it means if you remove or add more row items in the tables it will consider updated data every time you hit refresh button.

Prior understanding the code to create pivot, we need to understand the object model of Pivot table. which is as following –

  • Workbook

    • PivotCache

      • PivotTable

        • PivotFields

          • PivotItems

To create pivot table, First of all we need to add Pivot cache in the workbook and that will use the following code.

Set rngRange = Sheet1.Range("A1").CurrentRegion
Set objPivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngRange)

Once PivotCache is create we can add a pivot table. To create pivot table we need a location where we can insert it and that should be a cells address. following syntax will insert a pivot table

Set rngDestination = Sheet1.Range("J1")
Set objPivotTable = objPivotCache.CreatePivotTable(rngDestination, "PivotTable")

Now, pivot table is added and ready to add fields. This code is using —

.AddFields methods, you can specify the data headers which you want to add in ROW FIELD, COLUMN FIELD and in PAGE FIELDS of the pivot table.

.AddDataFields will add the data in DATA FIELD for aggregation. you can specify which formula you want to use for summarization.

 
    With objPivotTable
        .AddFields Array("Header 1", "Header 2"), "Header 3", "Header 4"
        Set objDataField = .PivotFields("Values 1")
        .AddDataField objDataField, , xlSum
    End With

Join our Excel VBA online training to learn more things like this

This is the entire code you can use to create a pivot table. This code have two more function to Remove fields and do filtering on fields in a pivot table.

Download this file PivotTable

'-- Thisworkbook.PivotCache.PivotTable.PivotFields.PivotItems

Sub CreatePivotTable()
    
    Dim objPivotCache As PivotCache
    Dim objPivotTable As PivotTable
    Dim rngRange As Range
    Dim rngDestination As Range
    Dim objDataField As Object
    Dim objField As Object
    
    '--Rule -- no blank field headers
    
    If Sheet1.PivotTables.Count = 0 Then
        Set rngRange = Sheet1.Range("A1").CurrentRegion
        Set objPivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngRange)
        Set rngDestination = Sheet1.Range("J1")
        Set objPivotTable = objPivotCache.CreatePivotTable(rngDestination, "PivotTable")
    Else
        Set objPivotTable = Sheet1.PivotTables(1)
        Set rngRange = Sheet1.Range("A1").CurrentRegion
        objPivotTable.PivotCache.SourceData = rngRange.Address(, , , 1)
        objPivotTable.PivotCache.Refresh
    End If
    
    '--Remove/Hide  Existing Fields
    RemoveFields objPivotTable
    
    '-- Add pivotFields
    
    With objPivotTable
        .AddFields Array("Header 1", "Header 2"), "Header 3", "Header 4"
        Set objDataField = .PivotFields("Values 1")
        .AddDataField objDataField, , xlSum
        '-- Filter Pivot tables
        Set objField = .PivotFields("Header 4")
        FilterPivotTable objField, Array("Header 4 - 3", "Header 4 - 2")
    End With
    
    '-- 2010 Excel Power programming with VBA

    
End Sub


Function RemoveFields(objPivotTable As PivotTable)
    
    Dim objField As PivotField
    For Each objField In objPivotTable.PivotFields
        objField.Orientation = xlHidden
    Next objField
    
End Function

Sub FilterField(objField As PivotField, varFilter)
    
    Dim objPivotItem As PivotItem
    Dim varEle
    Dim varMatch
    
    
    For Each objPivotItem In objField.PivotItems
        objPivotItem.Visible = True
    Next objPivotItem
    
    For Each objPivotItem In objField.PivotItems
        On Error Resume Next
        varMatch = 0
        '--Match will work only on one Dimensional array.
        varMatch = WorksheetFunction.Match(objPivotItem.Caption, varFilter, 0)
        On Error GoTo 0
            If varMatch = 0 Then
                objPivotItem.Visible = False
            End If
    Next objPivotItem
    
End Sub

Join our Excel VBA online training to learn more things like this

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

Send us mail