Manipulate Pivot Items With VBA

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:


Watch the video on YouTube

Further reading:
Filter Excel pivot table using VBA

Leave a Reply

Your email address will not be published. Required fields are marked *