VBA Pivot Table Collapse all fields

Question

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

Solution

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

Comments

[skkakkar]

Orientation of row fields should have positions also. Ex. ` With ActiveSheet.PivotTables("PivotTable3").PivotFields("Key Control ID") .Orientation = xlRowField .Position = 2 End With` Suppose you want to collapse entire field to "SOP Reference" Selecting a proper cell say A15code will be ` Range("A15").Select ActiveSheet.PivotTables("PivotTable3").PivotFields("Key Control Name"). _ PivotItems("electron").DrillTo "SOP Reference"` Does your table look like

[toucansame]

To be clear, you can do the same thing with the column fields, just change For Each pF In pT.RowFields to For Each pF In pT.ColumnFields