Creating a PivotTable Report : PivotTable « Excel « VBA / Excel / Access / Word

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"), _
         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


Related examples in the same category

1.Create Pivot Table From database
2.Modifying Pivot Tables
3.PivotTables Collection
4.Assign a value to the Orientation property of the PivotField object, as shown here:
5.Redefines the layout of the fields in the existing Table, apart from the data field
6.Add data field to PivotTable
7.Visible Property
8.add calculated items to a field using the Add method of the CalculatedItems collection
9.remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField:
10.adds a PivotTable based on the data from an Access database