Create Pivot Excel VBA (Visual Basic for Applications)

Create Pivot Excel VBA (Visual Basic for Applications)


A PivotTable is a powerful object in Excel to calculate, summarize, and analyze data that allows you see comparisons, patterns, and trends in your data in few clicks. A PivotTable object deals with following properties and methods which need to be set or edit to make Pivot automated:

PivotCache Methods

  • CreatePivotChart: It creates a standalone Pivot Chart from a PivotCache object which can be grabbed by a Shape object.
  • CreatePivotTable: It creates a Pivot Table based on a PivotCache object which can be grabbed in a PivotTable object.
  • MakeConnection: It establishes a connection for the specified PivotTable cache.
  • Refresh: This method help refreshing a specific PivotCache in Excel
  • ResetTimer: RefreshPeriod allows user to set a specific interval to refresh a pivot object.
  • SaveAsODC: It saves the PivotTable cache object as a Microsoft Office Data Connection file.

PivotCache Properties

  • ADOConnection
  • Application
  • BackgroundQuery
  • CommandText
  • CommandType
  • Connection
  • Creator
  • EnableRefresh
  • Index
  • IsConnected
  • LocalConnection
  • MaintainConnection
  • MemoryUsed
  • MissingItemsLimit
  • OLAP
  • OptimizeCache
  • Parent
  • QueryType
  • RecordCount
  • Recordset
  • RefreshDate
  • RefreshName
  • RefreshOnFileOpen
  • RefreshPeriod
  • RobustConnect
  • SavePassword
  • SourceConnectionFile
  • SourceData
  • SourceDataFile
  • SourceType
  • UpgradeOnRefresh
  • UseLocalConnection
  • Version
  • WorkbookConnection

Code example

Public Sub CreatePivotTable()
    'Declare range to bind Pivot
    Dim PivotRange As Range
    'Set range
    Set PivotRange = ActiveSheet.Range("A1:D40")
    'Declare pivot cache object
    Dim oPivotCache As PivotCache
    'Create and get reference of pivot
    Set oPivotCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange). _
    CreatePivotTable(TableDestination:=PivotSheet.Cells(2, 9), _
    'Memory cleanup
    Set oPivotCache = Nothing
    Set PivotRange = Nothing
End Sub
  1. ActiveWorkbook: It refers to current working workbook
  2. SourceType: xlDatabase is selected, it can be other database too
  3. SourceData: Refers range of source data on top you wish to create PivotTable
  4. TableDestination: Refers to the destination where you wish to have your PivotTable created
  5. TableName: Refers name of the table as per your choice

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Next: Areas object in Excel VBA

Leave a Reply

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