VBA/Excel/Access/Word/Excel/PivotTable

add calculated items to a field using the Add method of the CalculatedItems collection

 
     Sub AddCalculatedItem()
     With ActiveSheet.PivotTables(1).PivotFields("Product")
         .CalculatedItems.Add Name:="Melons", Formula:="=Mangoes*1.5"
     End With
     End Sub

Add data field to PivotTable

 
     Sub AddDataField()
         Dim pvt As PivotTable
         Set pvt = ActiveSheet.PivotTables(1)
         With pvt.PivotFields("Revenue")
             .Orientation = xlDataField
             .NumberFormat = "0"
         End With
         With pvt.DataFields("Sum of NumberSold")
             .Position = 2
             .Function = xlCount
             .NumberFormat = "0"
         End With
     End Sub

adds a PivotTable based on the data from an Access database

 
     Sub PivotTableDataViaADO()
         Dim con As ADODB.Connection
         Dim rs As ADODB.Recordset
         Dim sSQL As String
         Dim pvc As PivotCache
         Dim pvt As PivotTable
         Set con = New ADODB.Connection
         con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & CurrentProject.Path & "SalesDB.accdb;"
         sSQL = "Select * From SalesData"
         Set rs = New ADODB.Recordset
         Set rs.ActiveConnection = con
         rs.Open sSQL
         Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
         Set pvc.Recordset = rs
         Worksheets.Add Before:=Sheets(1)
         Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc, _
                 TableDestination:=Range("A1"))
         With pvt
             .NullString = "0"
             .SmallGrid = False
             .AddFields RowFields:="State", ColumnFields:="Product"
             .PivotFields("NumberSold").Orientation = xlDataField
         End With
     End Sub

Assign a value to the Orientation property of the PivotField object, as shown here:

 
     Sub AddFieldsToTable()
         With ActiveSheet.PivotTables(1)
             .AddFields RowFields:="State", AddToTable:=True
             .PivotFields("Date").Orientation = xlPageField
         End With
     End Sub

Create Pivot Table From database

 
Sub CreatePivotTableFromDB()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    Set PTCache = ActiveWorkbook.PivotCaches.Add (SourceType:=xlExternal)
    
    DBFile = ThisWorkbook.Path & "\budget.mdb"
    ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
    QueryString = "SELECT * FROM "" & ThisWorkbook.Path & "\BUDGET".Budget Budget"
    
    With PTCache
        .Connection = ConString
        .rumandText = QueryString
    End With
    
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"
    Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="BudgetPivot")
    
    With PT
        .PivotFields("DEPARTMENT").Orientation = xlRowField
        .PivotFields("MONTH").Orientation = xlColumnField
        .PivotFields("DIVISION").Orientation = xlPageField
        .PivotFields("ACTUAL").Orientation = xlDataField
    End With
End Sub

Creating a PivotTable Report

 
     Sub CreatePivotTable()
         Dim myWorksheet As Worksheet
         Dim pvc As PivotCache
         Dim pvt As PivotTable
         Set myWorksheet = Worksheets.add
         Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheet1.ListObjects("Table1").range)
         Set pvt = pvc.CreatePivotTable(TableDestination:=myWorksheet.range("A3"), _
                                      DefaultVersion:=xlPivotTableVersion12)
         With pvt
             With .PivotFields("Customer")
                 .Orientation = xlRowField
                 .Position = 1
             End With
             With .PivotFields("Product")
                 .Orientation = xlColumnField
                 .Position = 1
             End With
             .AddDataField .PivotFields("NumberSold"), "Sum of NumberSold", xlSum
         End With
     End Sub

Modifying Pivot Tables

 
Private Sub OptionButton1_Click()
    Application.ScreenUpdating = False
    With ActiveSheet.PivotTables(1).PivotFields("Month")
        .PivotItems("Jan").Visible = True
        .PivotItems("Q4").Visible = False
    End With
End Sub

PivotTables Collection

 
     Sub AddTable()
         Dim pvc As PivotCache
         Dim pvt As PivotTable
         Set pvc = ActiveWorkbook.PivotCaches(1)
         Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc,                  TableDestination:=Range("A3"))
     End Sub

Redefines the layout of the fields in the existing Table, apart from the data field

 
     Sub RedefinePivotTable()
         Dim pvt As PivotTable
         Set pvt = ActiveSheet.PivotTables(1)
         pvt.AddFields RowFields:=Array("Product", "Customer"), _
                      ColumnFields:="State", _
                      PageFields:="Date"
     End Sub

remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField:

 
Sub DeleteCalculatedItem()
     With ActiveSheet.PivotTables(1).PivotFields("Product")
         .PivotItems("Melons").Delete
     End With
End Sub

Visible Property

 
     Sub CompareMonths()
         Dim pvt As PivotTable
         Dim pvi As PivotItem
         Dim sMonth As String
         sMonth = "Jan"
         Set pvt = ActiveSheet.PivotTables(1)
         For Each pvi In pvt.PivotFields("Years").PivotItems
         If pvi.Name <> "2006" And pvi.Name <> "2007" Then
           pvi.Visible = False
         End If
         Next pvi
         pvt.PivotFields("Date").PivotItems(sMonth).Visible = True
         For Each pvi In pvt.PivotFields("Date").PivotItems
             If pvi.Name <> sMonth Then pvi.Visible = False
         Next pvi
     End Sub