A few Notes:
- I read it's better to use "DrillTo" vs "ShowDetail". I use both depending, but when I used the "Macro Recorder" it used "DrillTo"
- "ShowDetails" and "ShowDetail" both "work" in that neither gave errors, but only "ShowDetail" actually worked for me (No s)
- I couldn't work inside my main "With PivotTable.PivotFields" block where I build the table, I had to put it at the end after it was created
https://stackoverflow.com/questions/38246393/vba-pivot-table-collapse-all-fields
https://stackoverflow.com/questions/48157875/collapsing-one-pivot-field-among-many
https://docs.microsoft.com/en-us/office/vba/api/excel.range.group
Eg:
With ActiveSheet.PivotTables("PivotTable").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
.DataRange.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
'Seconds-->Minutes-->Hours-->Days-->Months-->Quarters-->Years
End With
ActiveSheet.PivotTables("PivotTable").PivotFields("Date").ShowDetail = False
PSheet.PivotTables("PivotTable").PivotFields("Months").ShowDetail = False ' Note you have to expand/collapse each element in the "Periods" array!
With PSheet.PivotTables("PivotTable")
.PivotFields("Years").ShowDetail = False
.PivotFields("Months").ShowDetail = False
.PivotFields("Years").PivotItems(Format(Now(), "yyyy")).DrillTo "Months"
End With