Auto-change to sum instead of count in Pivot

[kkfighter]

Hi All,

When i pull information into Pivot value, it always appear as count instead of sum. I need to manual change them back to sum and remove decimal place.
<TABLE style="WIDTH: 307pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=409 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=89></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Values</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Sum of Jan</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of Feb</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of Mar</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of Apr</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of May</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>Total</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>88,806,442 </TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD></TR></TBODY></TABLE>

Any idea how to change it to sum easily without manual change all of them.

I do not want to fill up the raw data with all zeros to make pivot recognise it with sum.

Thanks,
KK

 

[Claymationator]

Welcome to the board!

Give this code a try:

Sub SumAllData()
'changes data fields to SUM
On Error GoTo errHandler

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False

If PivotCheck(ws) Then
  For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False
  Next pt
Else
  MsgBox "There are no pivot tables on the active sheet"
End If

exitHandler:
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

Hope that helps.

 

[kkfighter]

thank you very much. it helps.

 

[Ngemini]

Hi, I am also facing similar issue and I tried using ur VB code in the editor and I ran it but this code doesnt work for me.
Error message is - Compile Error: Sub or Function not defined.

Could you please help.

Thanks,