Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
It' s possible to set a pivot subtotal with macro?
thank you very much
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
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
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
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
Hello,
I tried it on the V11 but it work but without this line of macro :
cp.TableProperties.PivotTablePosition = 0