Selecting / unselecting ALL pivot table items through VBA

[Gerald Higgins]

Hi All

http://www.mrexcel.com/forum/showthread.php?t=523764
I started this thread recently, didn't get a response, and managed to solve it myself as described.

However I now have a similar problem.

In a different pivot table, I want to do the equivalent of ticking (and later un-ticking) the (Show All) box on the pivot item drop down box.

When I tried the macro recorder, I got code which individually specified each pivot item, like this

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Location")
        .PivotItems("A B 2").Visible = False
        .PivotItems("A B 3").Visible = False

and so on, for however many pivotitems there are.

This kind of works, but it's cumbersome when there are many pivotitems.
And in future, the list of pivot items will change to include items that I can't predict.

What I really want is something like this

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Location")
        .PivotItems(ALL).Visible = False

which I could use to de-select all items, then go back and select 2 specific items which are pretty much always there.
I would then produce the report for those two items, and then go back and make all pivot items visible to show a separate report.

But I can't work out what the correct syntax for

        .PivotItems(ALL).Visible = False

actually is.

Any ideas ?

Thanks in advance.

By the way, using 2003.

 

[shg]

You're welcome.

Post back when you sort it out, in case I ever try to automate a pivot table

 

[Gerald Higgins]

Well, I finally fixed it !

Just in case anyone else has a similar problem, I'll recap on what I was trying to do.

I have a pivot table which does a number of different things.
The data refers to stock in a warehouse, and the pivot items that were causing me the problem were warehouse locations (storage bins if you prefer).
The number of these locations varies constantly, with new ones appearing all the time.

One task for the pivot table is to produce two reports.
The first report looked at two specific locations, which were (almost) always present in the underlying data.
It was easy to select the relevant two pivot items (and I could use a simple ON ERROR statement to deal with the rare occasions when one of them was missing).
But I was struggling with hiding all the other items, because I could not identify them individually, and I could not work out a way to hide them all at the same time.

The second report looked at every location EXCEPT these two.
Again, I could hide the two, but I couldn't work out how to select all the rest.

At the end, I wanted to make sure ALL pivot items were visible, to go and do some other stuff, and again, I couldn't work out how to make sure all items were visible.

The starting status is that all items are visible.

I used this code, suggested by shg4421, to hide everything except the two specific items

Dim LocnPivItm As PivotItem
    For Each LocnPivItm In ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").PivotItems
        Select Case LocnPivItm
            Case "A", "B"
            Case Else
                LocnPivItm.Visible = False
        End Select
    Next LocnPivItm

Bear in mind that Excel will not actually allow you to hide every single pivot item (or at least I don't know how to do it).
In the example above, it works as long as at least one of "A" or "B" are in the item list, because they remain visible
If neither of them are in the item list, the code will fall over when it tries to hide the very last item in the list.
If there is a risk that this could happen, you might want to use some kind of error handling.

This worked fine.

I then used this code to display ALL items.

Dim Pvt As PivotTable
Dim Pf As PivotField
Dim Pi As PivotItem
Set Pvt = ActiveSheet.PivotTables(1)
Set Pf = Pvt.PivotFields("Location")
    With Pf
    .AutoSort xlManual, .SourceName
        For Each Pi In .PivotItems
        On Error Resume Next
             Pi.Visible = True
        On Error GoTo 0
        Next Pi
    .AutoSort xlAscending, .SourceName
    End With

Without the On Error statement, this code initially fell over when it tried to make some items visible, and I could not work out why.
Adding the On Error statement made the code run correctly, but on inspecting the pivot table manually, I could see that there were still a handful of items that had not been selected for some reason.

I eventually worked out that these items were old data that were still in the Pivot Table cache, but were no longer in the underlying data.

I tested this by recording a macro to individually select an old item like this by name.
You can select an old pivot item manually, but it seems to be impossible to select it through code.
Once I realised that, I realised it wasn't really a problem, and I got round it in two ways, first by adding code to clear out pivot table cache, like this

For Each Pt In ActiveSheet.PivotTables
        Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next

and secondly, by basically realising that even if I didn't clear out the PT cache, it didn't matter if these items were not selected, because they weren't in the "real" data anymore, so it was irrelevant whether or not they were selected.

I think best practice should be to clear out the cache, anyway.

Many thanks again to shg4421, who was a great help on this one. Cheers !

 

[Osceana]

Well, I finally fixed it !...

Everyone who contributed to this post, thank you so much! This really got me out of a jam today.

 

[norulen]

Thanks to everyone for this post. I could fix my userform using the logics in this code

 

[fhodsoll]

I have used the following to select items in a pivotfield

Dim Pvt_Tble As PivotTable, pvtField As PivotField, pvtItem As PivotItem


For Each pvtField In pvtTable.PivotFields
If pvtField.Name = "Utility" Then
On Error GoTo ErrorHandler
pvtTable.PivotCache.Refresh


pvtField.ClearAllFilters

For Each pvtItem In pvtField.PivotItems
If pvtItem.Name = Util_SF_Nm Then
pvtItem.Visible = True
Else
pvtItem.Visible = False
End If
Next pvtItem


End If
Next pvtField


Hope this helps

 

[xlDude]

I'm not sure if anyone is following this or not, but I do have a solution I just figured out...

I used this for my worksheet named "Lists", pivot table named "customerRequester", and pivotField named "Requester"


Call changePivotChartToAll("customerRequester", "Requester")
Sub changePivotChartToAll(tblName As String, pivotField As String)
With Sheets("Lists").PivotTables(tblName).PivotFields(pivotField)
For x = 1 To .PivotItems.Count
If .PivotItems(x).Visible = False Then
.PivotItems(x).Visible = True
End If
Next x
End With
End Sub

Works like a charm

 

[Phydeaux]

These are all great suggestions, but is there a way to do it without using for/next loops? My table spans A1:CK6381. Even with application.screenupdating = false and application.calculation = xlCalculationManual, it takes several minutes to finish. When I filter manually it still takes several seconds, but it is understandable from the large amount of data. Is there something else I can turn off to make these subs faster?

 

[Phydeaux]

Well, I seem to have made my sub more efficient. Instead of selecting all with for/next then deselecting all with a case exclusion, I enabled what I needed with case then disabled only what was enabled with case.

Sub PvtChange()
    
    Dim oPI As PivotItem
    For Each oPI In ActiveSheet.PivotTables("PivotTable1").PivotFields("MFG").PivotItems
        Select Case oPI.Name
            Case "BF"
                If oPI.Visible = False Then oPI.Visible = True
        End Select
    Next oPI

    For Each oPI In ActiveSheet.PivotTables("PivotTable1").PivotFields("MFG").PivotItems
        Select Case oPI.Name
            Case "BF"
            Case Else
                If oPI.Visible = True Then oPI.Visible = False
        End Select
    Next oPI
    
End Sub

 

[juhan6]

I just wanted to thank all you guys for helping me solve the part of the problem. I was wondering what the code would be if I wanted to show everything except the two specific items. I have used the code on the bottom and switched the LocnPivItm.Visible = True however I was getting the run-time error 1004. I'm not sure where I'm going and would love to have some feed back. Thank you!

Sub ExcludeSpecificFields()

    Dim LocnPivItm As PivotItem
        For Each LocnPivItm In ActiveSheet.PivotTables("PivotTable4").PivotFields("Failure Code Class").PivotItems
            Select Case LocnPivItm
                Case "Excluded"
                Case Else
                    LocnPivItm.Visible = True
            End Select
        Next LocnPivItm
    
End Sub

 

[Phydeaux]

I think it falls on "LocnPivItm.Visible = True". Consider "If LocnPivItm.Visible = False then LocnPivItm.Visible = True". I don't think you can set the True property if it is already True.