Filter Pivot Table Based On Selected Value And Show All Results Though VBA

[hanishgautam]

Hi All,

Please find the excel file in which you can filter your result in pivot table according to dropdown selection and find the code give below:


Sub ShowAll()

Dim pt As PivotTable, pi As PivotItem
Dim xlCalc As XlCalculation

Set pt = Sheet1.PivotTables("PivotTable1")

pt.ManualUpdate = True

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False '
End With

On Error Resume Next

For Each pi In pt.PivotFields("StaffCode").PivotItems
pi.Visible = True
Next pi

On Error GoTo 0

pt.ManualUpdate = False

With Application
.Calculation = xlCalc
.ScreenUpdating = True
End With

End Sub


Sub ShowPivotFilterResult()

Dim pt As PivotTable, pi As PivotItem
Dim xlCalc As XlCalculation


Set pt = Sheet1.PivotTables("PivotTable1")

pt.ManualUpdate = True

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False '
End With

On Error Resume Next

For Each pi In pt.PivotFields("StaffCode").PivotItems
pi.Visible = True
Next pi

On Error GoTo 0


On Error Resume Next

For Each pi In pt.PivotFields("StaffCode").PivotItems
If pi.Value = Range("rngVALUE").Value Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi

On Error GoTo 0

pt.ManualUpdate = False

With Application
.Calculation = xlCalc
.ScreenUpdating = True
End With

End Sub

[Excel Fox]

Thanks Hanish. You can wrap your code using the code tags. To use code tags, either select your code and press the code tag button in the editor group, or type [Code]your code here[\Code] and use a forward slash instead of backward.

[hanishgautam]

Sure I will remember next time whenever i will post... :)

[snb]

This is a nonsense item.
Filtering can be accomplished in a table (listobject).
The sum of ID's is nonsense.

[Excel Fox]

snb, I tend to agree to your point (or concern, rather) regarding the above post, particularly since the pivot table is almost identical to a listobject on a sheet, and to filter a listobject, it doesn't take elaborate coding. I guess hanishgautam was just trying to showcase how one can filter a pivot table / pivot item.

In addition, I agree that Sum of ID doesn't feel logical at all. Again, the intent was probably just what I presumed above. Having said that, I would put it this way - hanishgautam could have used a more apt data example to showcase what he was trying to.

Let's just call it fair for this one.

hanishgautam, snb is a very skilled and admirable person among such forums, and I am sure you'll appreciate the gravity of snb's mind in this above post of yours. Let's just say snb likes to run a tight ship when it comes to coding and applying it to scenarios.

Cheers gentlemen.