Show All Pivot Items, Hide One Specific One

[brezinst]

Hello,

So I have a spreadsheet where I automatically update a PivotTable with a filter. Basically, it counts the number of brokers that we have a particular security with and then filters out any names where the amount is actually zero. The problem is, the pi.Visible = True line does not work. It causes a "Run-time error '1004': Unable to set the Visible property of the PivotItem class". The pi.Visible = False line works perfectly fine. Can someone please take a look at my code and see if they can get it to work? See below.

Thanks in advance!!!
Todd

Sub LoadSecurityList()
     
    Dim IngSortOrder As Long, strSortField As String
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem

        Set pt = ActiveSheet.PivotTables("PivotTable6")
        Set pf = pt.PivotFields("S/D Amount")
        
        pt.PivotCache.Refresh
        pt.ManualUpdate = True
        pf.EnableMultiplePageItems = True
        
        strSortField = pf.AutoSortField
        IngSortOrder = pf.AutoSortOrder
        
        pf.AutoSort xlManual, pf.SourceName
        
        For Each pi In pf.PivotItems
            If pi.Visible <> True Then
                pi.Visible = True
            End If
        Next pi
        
        For Each pi In pf.PivotItems
            If pi.Value = "0" Then
                pi.Visible = False
            End If
        Next pi
        
        pt.ManualUpdate = False
        
        pf.AutoSort IngSortOrder, strSortField

 

[PaulTee]

I hope someone helps as I have a similar problem. In fact this has been a known problem for quite a few years now yet it still appears yet to have been addressed. I've googled around a lot and tried many alleged fixes but with no joy. I'd hoped myself that the line 'pf.AutoSort xlManual, pf.SourceName' (suggested elsewhere) would solve it, but sadly no.
See the similarities here:
http://social.msdn.microsoft.com/forums/en-us/isvvba/thread/FC2625A9-A258-4183-8F61-6B1DF0E0DFE8
here
http://www.pcreview.co.uk/forums/macro-pivot-ms-excel-2003-vs-2007-a-t3577947.html
& here
http://support.microsoft.com/kb/114822

If I do find a 'proper' solution then I'll post up a reply here; in the meantime all we can do is wait...

 

[PaulTee]

I've been playing with my spreadsheet data source and noticed that if I stuck an 'On Error Resume Next' into the code then it picked a random(?) set of date values less than 10 in almost 300 (5 of which were in a row in the filter) - no logic at all.