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


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)
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