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]

Maybe ...

Dim oPI As PivotItem
 
For Each oPI In ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").PivotItems
    oPI.Visible = False
Next oPI

 

[Gerald Higgins]

SHG4421 - thanks very much for the suggestion, I will try it now.

 

[shg]

It's just a guess, Gerald, I've never automated a pivot table.

 

[Gerald Higgins]

This almost works, but not quite.

I can see that it is de-selecting each item in turn.
BUT, when it get's to the very last one, it can't de-select that one.
When I try and replicate this manually, Excel says I have to have at least one pivot item selected.

I do actually want two items to be selected, which are always the same and always present, let's call them A and B.
The fact that your code de-selects A and B is not in itself a problem, because I can then select them again afterwards.
BUT, if there was a way to get your code to de-select everything except A and B, that would be neat, and it might also get round the problem of Excel not letting you de-select all items at once.

Alternatively, there's also a blank item in the in the pivot item list - if the code de-selected everything except that one, that would be OK too.

Any ideas ?

 

[Gerald Higgins]

Maybe if there was some way of coding

For Each EXCEPT(A,B)

 

[shg]

Well, continuing to shoot in the dark ...

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

 

[Gerald Higgins]

Good shot !
This is working . . . .except . . . .

It's working fine for de-selecting all the items except the two I want to show.
That's fine.
I can then produce the report for those two items.
So far so good.

I then want to re-select all items, so that I can produce the report for all items.

I tried a slight modification of your code, and it looks like it's manually re-selecting them all, but then halfway through it stops, showing a message

Run Time Error 1004
Unable to set the Visible property of the PivotItem class

I tried adding in a select case stage to only make this apply to items that were not visible, but that doesn't seem to make any difference.

The code I am using to re-select all items is this

Dim LocnPivItm2 As PivotItem
    For Each LocnPivItm2 In ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").PivotItems
        Select Case LocnPivItm2.Visible
            Case True
            Case Else
        LocnPivItm2.Visible = True
        End Select
    Next LocnPivItm2

and the error is on the line in bold.

The weird thing is, when I look at the pivot table, most of the items have actually been selected again, there's only a handful of items at the bottom of the list that have not been selected, and I can manually then select all of them.
So I don't understand why the macro can't select them.
I can't see any pattern in them - the items that aren't selected look very similar to the ones that are.

Any ideas ?

Thanks again for your help so far, I'm learning alot as we go on !

 

[shg]

Gerald, I don't know enough about pivot tables to make a further suggestion, sorry.

This may be relevant, but it appears to apply only to Excel 5: http://support.microsoft.com/default.aspx?scid=kb;en-us;114822&Product=xlw

Debra Dalgleish has some code at http://www.contextures.com/xlPivot03.html#Show

 

[Gerald Higgins]

shg4421 - that code at contextures looks like a distinct possibility.

It will be a few days now before I get the chance to try it out, I'll post back then.

Thanks for the links - I really appreciate it.