Microsoft Office Tutorials and References
In Depth Information
20.5.4 PivotSelect and PivotSelection
20.5.4 PivotSelect and PivotSelection
The PivotSelect method selects part of a PivotTable. The syntax is:
PivotTableObject .PivotSelect( Name , Mode )
The Mode parameter specifies the selection mode and can be one of the following
XlPTSelectionMode constants:
Enum XlPTSelectionMode
xlDataAndLabel = 0
xlLabelOnly = 1
xlDataOnly = 2
xlOrigin = 3
xlBlanks = 4
xlButton = 15
xlFirstRow = 256 ' Excel 9 only
End Enum
The Name parameter specifies the selection in what Microsoft refers to as "standard PivotTable
selection format." Unfortunately, the documentation does not tell us what this means, saying
instead, "A string expression used to specify part of a PivotTable. The easiest way to understand
the required syntax is to turn on the macro recorder, select cells in the PivotTable, and then study
the resulting code." There is more on this, and we refer the reader to the Excel VBA help
documentation (start by looking up the PivotSelect topic).
So let us consider some examples, all of which are based on the pivot table in Figure 20-10 .
However, to illustrate the Name property and to shorten the figures a bit, we will rename the data
field "Sum of Transactions" to "Trans" and "Sum of Sales" to "Sale" using the following code:
Sub Rename()
' To shorten the names of the data fields
ActiveSheet.PivotTables("Sales&Trans"). _
DataFields("Sum of Transactions").Name = "Trans"
ActiveSheet.PivotTables("Sales&Trans"). _
DataFields("Sum of Sales").Name = "Sale"
End Sub
Search JabSto ::

Custom Search