Pivot Table from Data Model- Collapsing All Fields

[ryan42430]

I am looking for help with code to collapse all fields in a pivot table referencing the data model. I have found plenty of viable options with the pivot table is directly referencing a table, but nothing about referencing the data model. Most of the common function that work with a standard pivot table do not work with the data model due to the CUBE Function that goes along with the data table.

[HansV]

Welcome to Eileen's Lounge!

I'm not sure we have someone here who can help you. If you don't get a helpful suggestion you might consider posting on Microsoft Excel Community (please mention that you posted here to avoid being flamed for cross-posting)

[rory]

For a data model pivot, something like this should work:

Code: Select all

Sub Collapse_PP_Pivot(pt As PivotTable)
   Dim pf As PivotField
   For Each pf In pt.RowFields
      pf.DrilledDown = False
   Next pf
   For Each pf In pt.ColumnFields
      pf.DrilledDown = False
   Next pf

End Sub

just pass the required pivot table to it as an argument when calling it.