To deactivate subtotals in a pivot table with a macro

[TopLearner]

Hi everybody,

I hope you are well.


I have macro to create a pivot table but I would like to remove the subtotals in the field settings.

I mean to ask the macro to go onto field settings and then under the subtotals&filters tab > subtotals > select none.

Can you please help?

Thanks
Sanchez

 

[RoryA]

Assuming your macro already has a PivotTable object variable called pt, you can add something like this:

   pt.ManualUpdate = True
   Dim pf As PivotField
   For Each pf In pt.RowFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
   Next pf
   For Each pf In pt.ColumnFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
   Next pf
   pt.ManualUpdate = False

 

[TopLearner]

Assuming your macro already has a PivotTable object variable called pt, you can add something like this:

   pt.ManualUpdate = True
   Dim pf As PivotField
   For Each pf In pt.RowFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
   Next pf
   For Each pf In pt.ColumnFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
   Next pf
   pt.ManualUpdate = False

Many thanks Rory,

It works.
Just another related question, how about if now I what to add the PivotTable Analyze > Pivot Table > show report filter pages to the macro? How could I do so?

Thanks a bunch
Sanchez