CODEWITHSUNDEEP
vbaexcelpivot-table
Aidan Williamson
Aidan Williamson

Reputation: 125

VBA Pivot Table Collapse all fields

I've created a pivot table in VBA full of string data, but can't seem to collapse all the fields in the pivot table, how would I do this? Here's my source code

    SrcData = ActiveSheet.Name & "!" & Range(Cells(1, 1), Cells(46, 3)).Address(ReferenceStyle:=xlR1C1)
StartPvt = Sheets("Key Controls").Cells(2, 5).Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")
pvt.PivotFields("SOP Reference").Orientation = xlRowField
pvt.PivotFields("Key Control ID").Orientation = xlRowField
pvt.PivotFields("Key Control Name").Orientation = xlRowField

Upvotes: 6

Views: 15457

Answers (1)

R3uK
R3uK

Reputation: 14537

Do NOT use pf.ShowDetail = False :

It is a nightmare of efficiency, you'll get stuck for a LONG moment and probably crash Excel!

The good method to use is DrillTo :

Public Sub PivotTable_Collapse()

    Dim pT As PivotTable
    Dim pF As PivotField

    Set pT = ActiveSheet.PivotTables(1)

    With pT
        For Each pF In pT.RowFields
            pF.DrillTo pF.Name
        Next pF
    End With

End Sub

Upvotes: 9

Related Questions