Monday, March 21, 2016

How to filter pivot tables based on a cell value

Ever wondered how convenient would it be if all the filters of all the pivot tables changed based on a cell value.

Yes, you can do it using the below VBA code.

The steps are as follows.

1. Copy the below VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Application.ScreenUpdating = False
Dim PT As PivotTable
Dim pi As PivotItem
Dim strField1 As String
Dim strField2 As String


strField1 = "School"


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
a
If Target.Address = Range("B2").Address Then

For Each PT In PivotTables
With PT.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next PT
End If

If Target.Address = Range("B2").Address Then

For Each PT In PivotTables
With PT.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next PT

End If

Application.EnableEvents = True
Application.ScreenUpdating = False

End Sub

2. Right click on the excel sheet where you have your pivot tables. Now click on 'View code'



3. Now paste the code you have copied and make the following changes as applicable in your case.

  • Change the "Range(XX)" references in the code to the cell where you would input the filter value.

  • Change strField1 = "XXXXXX" to the actual field in the pivot able on which you would apply filter for.

4. Save the file as a Macro-enable excel file. Re-open the file and enter any available value for the field you want to filter for in the Range you have specified .


Done!! You have done it. :)

Well, I hope you have enjoyed this. Let me know if you want to know something more or have any specific question (questions.aweexcel@gmail.com)

No comments:

Post a Comment