Filtering out (blank) within a PivotFiled from all pivottables in a workbook that have the same pivotfields

Question

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.

Answer 1

I think you need to replace pf with PivotFields and pi with PivotItems