Manipulate Pivot Items with VBA



     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos





We can also manipulate the pivot items with VBA in MS Excel. For example, if our filter area contains data from different locations we can use VBA to display data from a specific region or all the locations. Today we'll use a very intuitive method to solve our problem. We'll record two macros, attach them to command buttons and after making simple additions we 'll learn how create macro code that helps us to manipulate the pivot items.
The recorded macro code is given below:

Sub showOne()
'
' showOne Macro
' Shows data of one location
'
' Keyboard Shortcut: Ctrl+o
'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").CurrentPage = _
        "New York"
End Sub

Sub showAll()
'
' showAll Macro
' Shows data of all locations
'
' Keyboard Shortcut: Ctrl+a
'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").CurrentPage = _
        "(All)"
End Sub


The macro code for the command button to display a specific location:
Private Sub CommandButton1_Click()
Dim mylocation As String
mylocation = InputBox("Enter a location", "Select Location")
ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").CurrentPage = _
       mylocation
End Sub

The VBA code for the command button to display all the locations:
Private Sub CommandButton2_Click()
ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").CurrentPage = _
        "(All)"
End Sub

of course we can use standard looping processes in VBA to manipulate data display from pivot tables. The VBA code is given below:

Sub displaySingleItem()
Dim myPivotField As PivotField
Dim myPivotItem As PivotItem
Set myPivotField = _
ActiveSheet.PivotTables(1).PivotFields(Index:="Location")
For Each myPivotItem In myPivotField.PivotItems
If myPivotItem.Name = "New York" Then
myPivotItem.Visible = True
Else
myPivotItem.Visible = False
End If
Next myPivotItem
End Sub

Sub displayAllItems()
Dim myPivotField As PivotField
Dim myPivotItem As PivotItem
Set myPivotField = _
ActiveSheet.PivotTables(1).PivotFields(Index:="Location")
For Each myPivotItem In myPivotField.PivotItems
myPivotItem.Visible = True
Next myPivotItem
End Sub

Watch the video:





Manipulate Pivot Table Items With VBA

Watch the video on YouTube

References
Filter Excel pivot table using VBA
Home More Excel (VBA) Training Videos