Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

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://stackoverflow.com/questions/38997449/vba-to-collapse-pivot-table-detail-for-prior-years/58617446#58617446

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
  • No labels