Excel VBA- Pivot Table How To Create (VBA)

By Ajeet Kumar

EXCEL VBA AN EXAMPLE TO CREATE A PIVOT TABLE 

The following steps are required to create a pivot table using VBA.

1. An instance of PivotCache is created by using Create Method of PivotCaches collection.
2. The Create method is passed source data range in case excel sheet provides the data.
3. An instance of PivotTable is created by using CreatePivotTable method of PivotTable object. The CreatePivotTable method is passed the Destination Cell where the pivot table will be generated.
4. The PIvotFields collection is indexded to refer pivot fields.
5. The properties are set for each field included in the pivot table.

Sub PivotModified()
    Dim ShtPT As Worksheet
    Dim rngSrcData As Range
    Dim rngDest As Range
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    For Each PT In Sheet3.PivotTables
        PT.TableRange2.Clear
    Next
    
    Set rngSrcData = Sheet2.Range("A1").CurrentRegion
    Set rngDest = Sheet3.Range("C3")

    Set PTCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngSrcData)
    Set PT = PTCache.CreatePivotTable(TableDestination:=rngDest)
    
    With PT.PivotFields(1)
            .Orientation = xlPageField
            .Position = 1
    End With
    
    With PT.PivotFields(2)
            .Orientation = xlRowField
            .Position = 1
    End With
    
    With PT.PivotFields(3)
            .Orientation = xlColumnField
            .Position = 1
    End With
    
    With PT.PivotFields(4)
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
    End With
    
    With PT.PivotFields(4)
            .Orientation = xlDataField
            .Function = xlCount
    End With
    
    Set rngSrcData = Nothing
    Set rngDest = Nothing
    Set PTCache = Nothing
    Set PT = Nothing

End Sub