How to select all pivot items with .PivotItems(?????).Visible = True

[BOYAR]

Hi Everyone,

Im trying to find easy solution on how to select all Pivot items. Things is that my data base is updated with new entries very often so i need to refresh pivot and then open filters and click on "select all" each time, and then un-check "blanks"

What i have now is :

Sub Pivot_upd()
'
' Pivot_upd Macro
'

'
    Sheets("Pivot").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Mfg. Part #")
        .PivotItems([B]"(Select ALL)"[/B]).Visible = True
        .PivotItems("(blank)").Visible = False
        
    End With
    
End Sub

Im not sure what is the proper command to be used instead of this "select all" ?

 

[BOYAR]

Ok found the solution:

Sub Pivot_upd()
'
' Pivot_upd Macro
'

'
    Sheets("Pivot").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Mfg. Part #")
        .ClearAllFilters
        .PivotItems("(blank)").Visible = False
                 
    End With
    
End Sub

 

[bisel]

Thank you, Boyar. For some reason, when I tried recording the macro, I did not get that code. But, this time I did.

Still did not work like I wanted, but taking your suggestion I found the solution that works.

I have two pivot tables and created this sub which seems to do the job ...

• Select all the items
• Then de-select the pivot items that equal zero

Sub RefeshPivots()

    Sheet4.PivotTables("PivotTable1").PivotCache.Refresh
        With Sheet4.PivotTables("PivotTable1").PivotFields("Years Total")
            .ClearAllFilters
            .PivotItems("$0").Visible = False
        End With
        
    Sheet4.PivotTables("PivotTable2").PivotCache.Refresh
        With Sheet4.PivotTables("PivotTable2").PivotFields("Years Total")
            .ClearAllFilters
            .PivotItems("$0").Visible = False
        End With

End Sub

Regards,

Steve