ateneva
4/22/2017 - 1:40 PM

Modify the summary functions for each pivot table in your workbook

Modify the summary functions for each pivot table in your workbook

Sub ModifyDataFieldsSummaryFunction()

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Dim i As Integer
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, 2014
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For i = 1 To 2
Worksheets(i).Activate
Set PT = ActiveSheet.PivotTables(1)

Select Case i

Case 1
ActiveSheet.name = Format(ActiveSheet.Range("K2"), "dd-mmm")
For Each PF In PT.DataFields
'must use DataFields Collection if you are going to change the method of consolidation
    If PF.Position > 4 Then PF.Function = xlCountNums
    If PF.Position <= 4 Then PF.Function = xlSum
    If PF.Position <= 4 Then PF.NumberFormat = "0.0"
Next PF

Case 2
ActiveSheet.name = "weeks" & Format(ActiveSheet.Range("K2"), "dd-mmm")

For Each PF In PT.DataFields
'must use DataFields Collection if you are going to change the method of consolidation
    If PF.Position > 3 Then PF.Function = xlCountNums
    If PF.Position <= 3 Then PF.Function = xlSum
    If PF.Position <= 3 Then PF.NumberFormat = "0.0"
    Next PF

End Select

Next i
End Sub