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