VBA/Excel/Access/Word/Excel/PivotTable

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

   <source lang="vb">

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

Add data field to PivotTable

   <source lang="vb">

    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
</source>
   
  

adds a PivotTable based on the data from an Access database

   <source lang="vb">

    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
</source>
   
  

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

   <source lang="vb">

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

Create Pivot Table From database

   <source lang="vb">

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

</source>
   
  

Creating a PivotTable Report

   <source lang="vb">

    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
</source>
   
  

Modifying Pivot Tables

   <source lang="vb">

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

</source>
   
  

PivotTables Collection

   <source lang="vb">

    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
</source>
   
  

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

   <source lang="vb">

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

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

   <source lang="vb">

Sub DeleteCalculatedItem()

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

End Sub

</source>
   
  

Visible Property

   <source lang="vb">

    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
</source>