1 Reply Latest reply: Mar 10, 2011 5:28 AM by Miguel Angel Baeyens de Arce RSS

    How to set "Subtotals on Top" in a Pivot Table via Macro?

      Is is possible to set "Subtotals on Top" in a Pivot Table via Macro?
      I have different dimensions and need a solution for setting "Subtotals on Top" individually.

      Thanks for your help.

        • How to set "Subtotals on Top" in a Pivot Table via Macro?
          Miguel Angel Baeyens de Arce

          Hello,

          Check the following code:

           

          Sub ChangeSubtotalPos Set c = ActiveDocument.GetSheetObject("CH01") '' Your chart ID here Set cp = c.GetProperties Set d = cp.Dimensions For i = 0 To d.Count -1 '' assuming all dimensions have partial sums d(i).ShowPartialSums = true '' make sure you are showing partial sums Next cp.TableProperties.PivotTableTotalPosition = 0 '0 = top; 1 = bottom c.SetProperties cpEnd Sub


          This will show partial sums and in top for first dimension ("d(0)", bold). If you want to show every dimension then you will need to loop through all the dimensions.

          Hope that helps.

          EDIT: Added loop, please check syntax