Counting values in a pivot table through VBA

[rdw72777]

So I have a pivot table and I'd like it to always re-populate with the rows always collapsed when data is refreshed or the data range changed.

Basically I have row labels for Region, State, DMA in that sequence in my pivot table. If a new region shows up, all of the States/DMA/s in the pivot table show up and are not collapsed, same with new states.

What I'd like to do is figure out a way I can go through all of the values in the Region pivot item and set the "ShowDetail" equal to false.

I can hardcode it using code similar to that below but I'm wary of that as it won't address new regions/states/DMA's.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").PivotItems( _
        "East").ShowDetail = False

I'm sure there must be a way to count the number of regions in the "region" pivot item and loop through to set them all to ShowDetail=False, but I can't figure out how to do a count on the pivotitem or pivotfield.

 

[Jerry Sullivan]

Hi Ryan,

In xl2007 you can collapse the field without iterating through each item.

Sub CollapseField1()
    ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("Region").ShowDetail = False
End Sub

Here are two ways to iterate just in case the code above doesn't work for you.

Sub CollapseField2()
    Dim pti As PivotItem
    With ActiveSheet.PivotTables("PivotTable1")
        For Each pti In .PivotFields("Region").PivotItems
            pti.ShowDetail = False
        Next
    End With
End Sub
 
Sub CollapseField3()
    Dim lngIdx As Long
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
        For lngIdx = 1 To .PivotItems.Count
            .PivotItems(lngIdx).ShowDetail = False
        Next
    End With
End Sub

 

[rdw72777]

Jerry

thanks, the code you gave worked perfect. It made me sad to know that changing from "pivotItem" to "PivotField" could have saved me hours of work the past 6 months, but I'm happy to never have to do it again.

Thanks again,

Ryan