to set a pivot subtotal with macro

[#1]

hi,

It' s possible to set a pivot subtotal with macro?

thank you very much

[Anonymous]

From the V10 API Guide:

set chart = ActiveDocument.Activesheet.CreatePivotTable
chart.AddDimension "ProductType"
chart.AddExpression "sum(Amount)"
chart.AddExpression "count(Customer)"
set cp = chart.GetProperties
set dims = cp.Dimensions
dims(0).ShowPartialSums = false    'do not show sums
chart.SetProperties cp

You will want to change the dims(0).ShowPartialSums = true

-Phil

[#3]

Sub SetSubTotalPivotTable

     Set c = ActiveDocument.GetSheetObject("CH01")

     Set cp = c.GetProperties

     Set d = cp.Dimensions

     For i = 0 to d.Count - 1

          d(i).ShowPartialSums = true

     Next

     cp.TableProperties.PivotTablePosition = 0

     c.SetProperties cp

End Sub

[#4 · Accepted solution]

Sub SetSubTotalPivotTable

     Set c = ActiveDocument.GetSheetObject("CH01")

     Set cp = c.GetProperties

     Set d = cp.Dimensions

     For i = 0 to d.Count - 1

          d(i).ShowPartialSums = true

     Next

     cp.TableProperties.PivotTablePosition = 0

     c.SetProperties cp

End Sub

[#5]

Hello,

I tried it on the V11 but it work but without this line of macro :

cp.TableProperties.PivotTablePosition = 0