0
I have a bunch (14) of pivottables in two worksheets (7 in each worksheet).
I have a refresh button on the sheet that refreshes all pivottables using the following code:
Sub Refresh_Pivots()
Dim pt As PivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each pt In WS.PivotTables
pt.RefreshTable
Next pt
Next WS
End Sub
All the pivottables have similar structure and there is one filed called "Month" in all pivottables. I need to filter out "(blank)" from the PivotField "Month" in all PivotTables when I click Refresh.
For that I added this one line under the Nested For Loop after pt.RefreshTable -
pt.pf("month").pi("(blank)").ShowDetail = False
So the changed code is:
Sub Refresh_Pivots()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each pt In WS.PivotTables
pt.RefreshTable
pt.pf("month").pi("(blank)").ShowDetail = False
Next pt
Next WS
End Sub
But, I'm getting a compilation error. I cannot figure out why. I'm relatively new to VBA so I'm sorry if this is a silly question.
Also, I need those blanks in the data, just need to filter them out from the PivotTables. Thanks.