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.

 

[juhan6]

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.

Thanks for your input. It still gives me a Run-time error 1004

Unable to set the Visible property of the PivotItem class

 

[Phydeaux]

Try adding ".Name" to the end of "Select Case LocnPivItm"

 

[DeltaOne1]

Here you go:

Sub PT_Items()
   ' Makes all (or defined) Items for specified Pivot Field visible/not visible.
    
   Dim p_pvtTable As PivotTable
   Dim pvfField   As PivotField
   Dim pviItem    As PivotItem
   ' Return reference to selected PivotTable field.
   Set p_pvtTable = ActiveSheet.PivotTables("yourPivotName")
   Set pvfField = p_pvtTable.PivotFields("yourPivotField")
   
   For Each pviItem In pvfField.PivotItems
        On Error Resume Next 'skip error when the item doesn't exsits
            Select Case pviItem.Name
                Case "A", "B"
                    pviItem.Visible = False
                Case Else
                    pviItem.Visible = True
                End Select
        On Error GoTo 0 'de-activate the error management
   Next
   
End Sub

 

[juhan6]

Here you go:

Wow it works great. If I flip the False and True, it will only select the ones I want.

Thank you so much!!!

 

[Phydeaux]

It should. One problem with the example posted by Delta is if everything goes false before something becomes true, you will get an error. At least one item must be selected at all times. That's why I used two separate for loops, setting the trues first. The on error statement above can potentially leave something selected that shouldn't be.

 

[ttat01]

It should. One problem with the example posted by Delta is if everything goes false before something becomes true, you will get an error. At least one item must be selected at all times. That's why I used two separate for loops, setting the trues first. The on error statement above can potentially leave something selected that shouldn't be.

I have the same issue. Still looking for workaround....

 

[ttat01]

I didn't find edit button, so I added new post.
I had found the solution for myself.

...
            For Each ptItemTemp In pt.PivotFields(".....").PivotItems
                If ptItemTemp.Visible = True Then Exit For
            Next ptItemTemp


            Debug.Print ptItemTemp.name & " is visible"


            pt.PivotFields(".........).PivotItems(ActiveSheet.Range("B1").Value).Visible = True ' ActiveSheet.Range("B1").Value stores the item that should become visible
            ptItemTemp.Visible = False
            
 ....

 

[CoRrRan]

It is a rather old thread, but, seeing the replies spanning the last couple of years, apparently still a topic for people like me who turn to Google to find their answer to this issue and are directed to this thread.

I have a simple solution to avoid the use of On Error Resume Next:

Dim oPT As PivotTable
Dim oPI As PivotItem

Set oPT = Worksheets("YourWorksheet").PivotTables("YourPivotTable")

With oPT.PivotFields("YourPivotFieldToFilter")
  
  .EnableMultiplePageItems = True  ' Could be superfluous
  .CurrentPage = "(All)"           ' Could be superfluous
  
  For Each oPI In .PivotItems
    If YourFilterCriteria = True [B]And oPI.RecordCount <> 0[/B] Then ' If the PivotField PivotItem holds no records, don't show it.
      oPI.Visible = True
    Else
      oPI.Visible = False
    End If
  Next oPI

End With

 

[meggo]

Hi there,
I was struggling with this for a while and then I realised it was just a simple tweak to my code using this reference by Ger Plante(Macro to filtering only ("(Blank)") in pivot table

However, I am using the variable FilterItem to reference a list. - See Ger Plante for just a simple item name.

Dim FilterItem As String
Dim pi As PivotItem

FilterItem = Worksheets("NameOfSheet").Range("A2")


With ActiveSheet.PivotTables("PivotName").PivotFields("FilterName")

' the for loop clears the existing filters

.PivotItems(FilterItem).Visible = True
For Each pi In .PivotItems
If pi.Name <> FilterItem Then
pi.Visible = False
End If
Next
.PivotItems(FilterItem).Visible = True
End With


.....
Replace:
FilterItem
NameOfSheet
PivotName
FilterName

I hope this helps.