PDA

View Full Version : VBA to change filters in pivot one after other



kevvukeka
06-06-2013, 02:52 AM
Hi All,

I have two pivots in sheets. For each of these I have Filters at the top. I need to change one filter at a time and then copy the relevant data to a new sheet. I could try the below code but as always I end up getting nothing. What I basically want is the filter to change one after another for the pivots, in between I can add the code the code to copy the data to a new sheet.

Below is the one which I tried:

Public Sub Filter()
Application.ScreenUpdating = False

Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField

For Each pt In ActiveSheet.PivotTables
'goes through each pivot table on the sheet
Set pf = pt.PivotFields("Provider Name")
For Each pi In pf.PivotItems

If Not pi.Name = "(blank)" Then
pi.Visible = True

Else
'do nothing

End If
Next pi
Next pt

Application.ScreenUpdating = True
End Sub