Pivot Table - VBA - Format Field Settings

[silentbuddha]

Hi,

I have 1 pivot table with over 10 Field List items. I would like to know how I can change the Field Setting... from 'count' to 'sum' for all the values in the Data Area.

Thank you !

 

[silentbuddha]

Hi,

This is how my pivot table looks like

<TABLE style="WIDTH: 362pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=483 border=0 name="metricOutputTable" x:str><COLGROUP><COL style="WIDTH: 203pt; mso-width-source: userset; mso-width-alt: 9874" width=270><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=3 width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl142 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 203pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=270 height=17>Data</TD><TD class=xl146 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right width=71 x:num="40174">12/27/2009</TD><TD class=xl144 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #c0c0c0; WIDTH: 53pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right width=71 x:num="40181">1/3/2010</TD><TD class=xl144 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #c0c0c0; WIDTH: 53pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right width=71 x:num="40188">1/10/2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl142 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Count of Calls Offered</TD><TD class=xl142 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl145 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl145 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl143 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Count of Calls Handled</TD><TD class=xl143 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl143 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Count of % Abandonned Calls</TD><TD class=xl143 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR></TBODY></TABLE>

What I would like to do: is loop though all the worksheets and within each worksheet change all the field setting from Count to Sum.

Here is the code I have so far:

Private Sub formatAllPivotTableDataArea()

Dim ws As Worksheet
Dim pvtTable As PivotTable
Dim pvtField As PivotFields

'don't allow screen updating because it takes time
Application.ScreenUpdating = False

'loop through each worksheet
For Each ws In ThisWorkbook.Worksheets

MsgBox ws.Name

'loop through each pivot table in the current worksheet
For Each pvtTable In ws.PivotTables


'refresh the pivot table
pvtTable.PivotCache.Refresh
Next pvtTable

'tell the user you are done with the refresh for each pivot table in current worksheet
MsgBox "The worksheet " & ws.Name & " and the pivot table are refreshed."


Next ws

End Sub

Thanks in advance

 

[silentbuddha]

Hi All,

Would anyone be able to assist

thanks in advance !

 

[silentbuddha]

Hi All,

I believe I have solved my problem...here is the code

Private Sub formatAllPivotTableDataArea()
Dim ws As Worksheet
Dim pvtTable As PivotTable
Dim pvtField As PivotField

'don't allow screen updating because it takes time
Application.ScreenUpdating = False
'loop through each worksheet
For Each ws In ThisWorkbook.Worksheets
MsgBox ws.Name
'loop through each pivot table in the current worksheet
For Each pvtTable In ws.PivotTables

'This command changes the formatting of the data values area from "Count of" to "Sum of"
For Each pvtField In pvtTable.DataFields

'MsgBox pf
pvtField.Function = xlSum
pvtField.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next pvtField
'refresh the pivot table
pvtTable.PivotCache.Refresh

Next pvtTable
'tell the user you are done with the refresh for each pivot table in current worksheet
MsgBox "The worksheet " & ws.Name & " and the pivot table are refreshed."

Next ws
End Sub