Menu
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
800
Right-click the item, click Expand/Collapse, and then do one of the following: To see the details for the current item, click Expand. To hide the details for the current item, click Collapse. To hide the details for all items in a field, click Collapse Entire Field.
There are shortcuts for this on the right-click menu and the Options/Analyze tab in the ribbon. We can also press the Expand Field and Collapse Field buttons on the Analyze/Options tab of the ribbon. A cell inside the Rows or Columns area of the pivot table must be selected for these buttons to work (be enabled).
Turn Off Automatic Date Grouping On the Ribbon, click the File tab, then click Options. At the left, click the Data category. At the end of the Data options section, add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables" Click OK to apply the new settings.
Hide the Expand and Collapse Buttons Select a cell in the pivot table. On the Ribbon, under PivotTable Tools tab, click the Analyze tab. Click the +/- Buttons command, to toggle the buttons on or off.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
