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

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