Macro Sheets Only
Returns information about a PivotTable.
Syntax
GET.PIVOT.TABLE(type_num,pivot_table_name)
Type_num is a value from 1 to 22 that represents a type of information you want about a PivotTable.
Type_num Information
1 Returns the name of the person who last updated the PivotTable, as a text constant.
2 Returns the date the PivotTablewas last updated, as a serial number.
3 Returns a horizontal array of text constants representing all the fields in the PivotTable.
4 Returns an integer representing the number of fields in the PivotTable.
5 Returns a horizontal array of text constants representing all the visible fields in the PivotTable(rows, columns, pages or data)
6 Returns a horizontal array of text constants representing all the hidden fields in the PivotTable. Return #N/A if no hidden fields.
7 Returns a horizontal arra of text constants representing the names of all the fields currently showing in the PivotTable as row fields. Returns #N/A if there are no row fields.
8 Returns a horizontal array of text constants representing all the fields currently showing in the PivotTable as column fields. Returns #N/A if no column fields exist.
9 Returns a horizontal arra of text constants representing all the fields currently showing in the PivotTable as page fields. Return #N/A if no page fields exist.
10 Returns a horizontal array of text constants representing all the fields currently showing in the PivotTable as data fields. Returns #N/A if there are no data fields.
11 Returns the smallest rectangular reference which bounds the PivotTable and all headers (not including the page header). This reference is returned as text.
12 Returns the smallest rectangular reference which bounds the PivotTable and all headers (including the page headers). This reference is returned as text.
13 Returns the reference to the row header area as text. The row header area includes each row field header along with all the items in each row field. Returns #N/A if there are no row headers.
14 Returns the reference to the column header area as text. The column header area includes each column field header along with all the items in each column field. Returns #N/A if there are no column headers.
15 Returns the reference to the data header area as text. The data header area includes the data field header along with all the headers in the data row/col. Returns #N/A if there is no data field.
16 Returns a reference to all the page headers as text.
17 Returns the reference to the PivotTable data area as text.
18 Returns TRUE if the PivotTable is set to show row grand totals.
19 Returns TRUE if the PivotTable is set to show column grand totals.
20 Returns TRUE if the user is saving data with the PivotTable.
21 Returns TRUE if the PivotTable is set up to Autoformat on pivoting.
22 Returns the data source of the PivotTable. The kind of information returned depends on the data source:
If the data source is a Microsoft Excel list or database, the cell reference is returned as text.
If the data source is an external data source, then an array is returned. Each row consists of a SQL connection string with the remaining elements as the query string broken down into 200 character segments.
If the data source is Multiple Consolidation ranges, then a two dimensional array is returned, each row of which consists of a reference and associated page field items.
If the data source is another PivotTable, then one of the above three kinds of information is returned.
Pivot_table_name is the name of a PivotTable containing the field that you want information about. If omitted, uses the PivotTable containing the active cell.
Remarks
Returns #VALUE! error value when pivot_table_name is not a valid PivotTable name on the active sheet and the active cell is not within a PivotTable.
Related Functions
GET.PIVOT.FIELD Returns information about an item in a PivotTable.
GET.PIVOT.ITEM Returns information about a PivotTable.
List of Information Functions