Microsoft Office Tutorials and References
In Depth Information
Manipulating PivotItems with VBA
MAnipulATing piVoTiTEMs WiTH VBA
PivotItems are programmable in PivotTables, and as an example, you can arrange to see just one
particular PivotItem in a field. In a PivotTable that was created earlier in the lesson, a Region field
was added. Suppose you want to see activity only for the North PivotItem , and hide the South,
East, and West PivotItems . The following macro will accomplish that.
Sub ShowSingleItem()
Dim objPivotField As PivotField
Dim objPivotItem As PivotItem
Set objPivotField = _
ActiveSheet.PivotTables(1).PivotFields(Index:=”Region”)
For Each objPivotItem In objPivotField.PivotItems
If objPivotItem.Name = “North” Then
objPivotItem.Visible = True
Else
objPivotItem.Visible = False
End If
Next objPivotItem
End Sub
The following macro will show all the PivotItems :
Sub ShowAllItems()
Dim objPivotField As PivotField
Dim objPivotItem As PivotItem
Set objPivotField = _
ActiveSheet.PivotTables(1).PivotFields(Index:=”Region”)
For Each objPivotItem In objPivotField.PivotItems
objPivotItem.Visible = True
Next objPivotItem
End Sub
crEATing A piVoTTABlEs collEcTion
PivotTables are objects for which there is a Collection object, just as there is for worksheets
and workbooks. As you might guess, the name of the Collection object for PivotTables is
PivotTables , and you can loop through every PivotTable on a worksheet, or throughout the
workbook if you need to.
For example, if you have more than one PivotTable on a worksheet and they are tied to the same
source list that starts in cell A1, this Worksheet_Change event would refresh all PivotTables on that
worksheet automatically when the source data is changed:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“A1”).CurrentRegion) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
Dim PT As PivotTable
For Each PT In ActiveSheet.PivotTables
PT.RefreshTable
Next PT
End Sub
Search JabSto ::




Custom Search