PivotTableWizard Method

Applies To

Worksheet Object.

Description

Creates a PivotTable. This method does not display the PivotTable Wizard.

Syntax

object.PivotTableWizard(sourceType, sourceData, tableDestination, tableName, rowGrand, columnGrand, saveData, hasAutoFormat, autoPage)

object

Required. The Worksheet object.

sourceType

Optional. Describes the source of the PivotTable data, as shown in the following table. If you specify this argument, you must also specify sourceData.

Value

Meaning

xlConsolidation

Multiple consolidation ranges

xlDatabase

Microsoft Excel list or database

xlExternal

Data from another application

xlPivotTable

Same source as another PivotTable


If sourceType and sourceData are not specified, Microsoft Excel assumes that the source type is xlDatabase, and the source data comes from the named range Database. If the named range does not exist, Microsoft Excel uses the current region if the current selection is in a range of more than 10 cells containing data. If this is not true, this method will fail.

sourceData

Optional. The data for the new PivotTable. A Range, an array of ranges, or a text constant representing the name of another PivotTable. For an external database, this is a two-element array. The first element is the connection string specifying the ODBC source for the data. The second element is the SQL query string used to get the data. If you specify this argument, you must specify sourceType. If the active cell is inside the sourceData range, you must specify tableDestination.

tableDestination

Optional. A Range specifying where the PivotTable should be placed on the worksheet. If this argument is not specified, the PivotTable is placed at the active cell.

tableName

Optional. The name of the PivotTable to be created, given as a string.

rowGrand

Optional. If True, the new PivotTable shows row grand totals. If False, row grand totals are omitted.

columnGrand

Optional. If True, the new PivotTable shows column grand totals. If False, column grand totals are omitted.

saveData

Optional. If True, data is saved with the table. If False, only the table definition is saved.

hasAutoFormat

Optional. If True, Microsoft Excel automatically formats the PivotTable when it is refreshed or when fields are moved.

autoPage

Optional. Valid only if sourceType is xlConsolidation. If True, Microsoft Excel creates a page field for the consolidation. If False, you must create the page field or fields.

Example

This example creates a new PivotTable from a Microsoft Excel database (contained in the range A1:C100).


ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")