Cycle through pivot table combinations 1

[hcisteve]

I am using Excel VBA and I want to be able to use VBA to cycle through all of the possible combinations of a pivot table. I used the Macro recorder but all it does is record the specific items. For example:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Group").CurrentPage = _
"(All)"

I want to be able to cycle through all of the items in group beginning with "(All)" and going through the rest.

I have tried various syntax changes, none of which have worked.

 

[SkipVought]

Hi,

    Dim pit As PivotItem
    For Each pit In ActiveSheet.PivotTables(1).PageFields(1).PivotItems
        MsgBox pit.Value
    Next

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[hcisteve]

Thanks. This is a good start but what I want to do is to select each of those values for the pivot table so that it changes the pivot table based on that selection. That is the part I am having trouble with. Sorry about not being clear on this before.

 

[KenWright]

What exactly are you looking to get out of this?

If you are looking to try and get a separate sheet for each of these values, then set the report up the way you want it, drag the field you want to break on into the Page Fields, and then use the Show Pages option to generate all the individual sheets automatically.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

    Dim pit As PivotItem
    With ActiveSheet.PivotTables(1).PageFields(1)
        For Each pit In .PivotItems
            .CurrentPage = pit.Value
        Next
    End With

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[hcisteve]

Thanks to both of you. The Show Page command is cool. But I was interested in how to do this in VBA. I tried the code you recommended and got the error message
"Invalid or unqualified reference."

 

[hcisteve]

Oh, I should also mention that .PivotItems is the term that was highlihgted when the error message was recieved.

Steve

 

[SkipVought]

Please post the code that you currently have.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[hcisteve]

Here it is.

Sub pivot2()
'

Dim pit As PivotItem
For Each pit In ActiveSheet.PivotTables(1).PageFields(1)
For Each pit In .PivotItems
'MsgBox pit.Value
.CurrentPage = pit.Value
Next

Next
End Sub

 

[SkipVought]

You CHANGED the code I posted at 24 Mar 05 13:13. No wonder!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[hcisteve]

OK I fixed the next and changed it to "End With". The MsgBox statement is commented out. I still get the same error message.
Sub pivot2()
'

Dim pit As PivotItem
For Each pit In ActiveSheet.PivotTables(1).PageFields(1)
For Each pit In .PivotItems
'MsgBox pit.Value
.CurrentPage = pit.Value
Next

End With
End Sub

 

[SkipVought]

I GIVE UP!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[SkipVought]

COPY 'N' PASTE THE CODE I POSTED!

YOUR code is STILL incorrect!!!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[hcisteve]

You were right. But I don't see what I did that was different?

Thanks,

Steve

 

[KenWright]

You posted this:-

    Dim pit As PivotItem
    For Each pit In ActiveSheet.PivotTables(1).PageFields(1)
        For Each pit In .PivotItems
            .CurrentPage = pit.Value
        Next
    End With

Skip posted this:-

    Dim pit As PivotItem
    With ActiveSheet.PivotTables(1).PageFields(1)
        For Each pit In .PivotItems
            .CurrentPage = pit.Value
        Next
    End With

Difference being

For Each pit In ActiveSheet.PivotTables(1).PageFields(1)
  vs
With ActiveSheet.PivotTables(1).PageFields(1)

Take a look at the Dim statement, eg

    Dim pit As PivotItem

Now take a look at what you changed the code to:-

    For Each pit In ActiveSheet.PivotTables(1).PageFields(1)

There are no pivotitems in the pagefields collection, there are only pagefields in the pagefields collection.

There are also only pivotItems in the PivotItems collection, hence the 'For each pit....'

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

    Dim pit As PivotItem
    [b]With ActiveSheet.PivotTables(1).PageFields(1)[/b]
        For Each pit In  .PivotItems
            [b].CurrentPage[/b] = pit.Value
        Next
    End With

.CurrentPage & .PivotItems referr to
With ActiveSheet.PivotTables(1).PageFields(1)

Your code have them referring to NOTHING AT ALL!!!!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[hcisteve]

Got it. Thanks to both of you!