Pivot Table from Data Model- Collapsing All Fields

ryan42430
NewLounger
Posts: 1
Joined: 21 Sep 2022, 12:05

Pivot Table from Data Model- Collapsing All Fields

Post by 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.

User avatar
HansV
Administrator
Posts: 74684
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pivot Table from Data Model- Collapsing All Fields

Post by 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)
Regards,
Hans

User avatar
rory
5StarLounger
Posts: 773
Joined: 24 Jan 2010, 15:56

Re: Pivot Table from Data Model- Collapsing All Fields

Post by 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.
Regards,
Rory