Microsoft Office 2000/Visual Basic Programmer's Guide

Creating a PivotTable Report from an Excel Range or Another PivotTable Report

To create a PivotTable report from an Excel range or another PivotTable report, you can use the PivotTableWizard method of a Worksheet or PivotTable object. The PivotTableWizard method allows you to specify multiple characteristics of the PivotTable report as arguments to the method.

All of the arguments of the PivotTableWizard method are optional, but you'll probably want to specify values for at least the SourceType and SourceData arguments. To create a PivotTable report from an Excel range, specify xlDatabase for the SourceType argument, and the range containing the data for the SourceData argument. To create a PivotTable report from an existing PivotTable report, specify xlPivotTable for the SourceType argument, and set a reference to an existing PivotTable report for the SourceData argument.

Note   When you call the PivotTableWizard method, you can't mix named and positional arguments. You must pass in all arguments either by name or by position.

The following code fragment creates a PivotTable report from the data range in a worksheet.

' Return used data range on Order Details Extended worksheet.
Set wksData = ThisWorkbook.Worksheets(ORDER_DETAILS_EXTENDED)
Set rngData = wksData.UsedRange

' Create PivotTable report. Start at cell B5 so there is enough 
' room for page fields.
Set pvtTable = wksData.PivotTableWizard(SourceType:=xlDatabase, _
   SourceData:=rngData, TableDestination:=wksPivot.Range("B5"))

Note   Calling the PivotTableWizard method creates a PivotTable report, but doesn't add any fields to the table, so you won't see any data in the PivotTable report until you add them. You can add fields either by dragging them from the PivotTable toolbar or by setting the Orientation property of the PivotField object that corresponds to a given field.

The preceding code fragment is taken from the CreatePivotTableFromXLRange procedure in the modPivotTable module in the Northwind.xls sample file, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.

For an example of creating a PivotTable report from an existing PivotTable report, see the CreatePivotTableFromPivotTable procedure in the modPivotTable module of the Northwind.xls sample file, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.