Hi, my VBA below is working fine, something in this code had caused all the pivot tables in each of my worksheet to filter a pivot field in tandem. But I would like each pivot to apply the filter independently. I think it has something to do with the Cache command. Can you please help?
VBA Code:
Sub Filter_Pivot()
Dim WS_Count As Integer
Dim i As Integer
Dim x As Integer
Dim ws As Worksheet
' Set WS_Count equal to the number of worksheets in the active workbook
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
Worksheets(i).Activate
If Worksheets(i).name <> "Data" Then
With Worksheets(i).PivotTables("PivotTable1")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
With Worksheets(i).PivotTables("PivotTable1").PivotFields("Name").ClearAllFilters
End With
With Worksheets(i).PivotTables("PivotTable1").PivotFields("Name")
'---hide all items except item 1
For x = 1 To .PivotItems.Count
If x = i Then
.PivotItems(x).Visible = True
Else
.PivotItems(x).Visible = False
End If
Next x
Worksheets(i).Rows("17:23").Hidden = True
End With
End With
End If
Next i
End Sub