Pivot TableChanging Values to Currency

[JulieO]

I have a pivot table with several columns and I would like to change the number values to currency - $x,xxx.00. Is there a way to do this without selecting each column separately and selecting the number formating?

 

[Trevor G]

You can do that with in the design of the pivot table, select a number field and Select Value Field Settings then look at the number format.

 

[JulieO]

Thanks Trevor - I'm actually looking for VBA code that would do this automatically since the pivot table I'm working with has several! columns.

 

[Trevor G]

I just recorded a macro to do this, see if you can adapt it.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Macro1 Macro</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br>    Range("F9").Select<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Values2")<br>        .NumberFormat = "£#,##0.00"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Range("D10").Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[JulieO]

Thanks again, but I need the code to change the formatting to currency in all of the columns. Moving from one column to the next automatically. The code you provided would change one column ("F").

 

[Trevor G]

What about using some with statements in your code screen, obviously without knowing your field names etc but here is a sample of a with statement. Have you at least got code for your Pivot Table and if so can you display it.

With ws.PivotTables("Table1").PivotFields("Sum totals")
.Caption = "Totals."
.NumberFormat = "$ #.##0,00"
End With

 

[Trevor G]

JulieO

This link will provide you with the code to update all fields

http://www.mrexcel.com/forum/showthread.php?t=499984

It is using this code

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> formatAllPivotTableDataArea()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> pvtTable <SPAN style="color:#00007F">As</SPAN> PivotTable<br><SPAN style="color:#00007F">Dim</SPAN> pvtField <SPAN style="color:#00007F">As</SPAN> PivotField<br><br><SPAN style="color:#007F00">'don't allow screen updating because it takes time</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#007F00">'loop through each worksheet</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>MsgBox ws.Name<br><SPAN style="color:#007F00">'loop through each pivot table in the current worksheet</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pvtTable <SPAN style="color:#00007F">In</SPAN> ws.PivotTables<br><br><SPAN style="color:#007F00">'This command changes the formatting of the data values area from "Count of" to "Sum of"</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pvtField <SPAN style="color:#00007F">In</SPAN> pvtTable.DataFields<br><br><SPAN style="color:#007F00">'MsgBox pf</SPAN><br>pvtField.Function = xlSum<br>pvtField.NumberFormat = "£#,##0.00_);[Red](£#,##0.00)"<br><SPAN style="color:#00007F">Next</SPAN> pvtField<br><SPAN style="color:#007F00">'refresh the pivot table</SPAN><br>pvtTable.PivotCache.Refresh<br><br><SPAN style="color:#00007F">Next</SPAN> pvtTable<br><SPAN style="color:#007F00">'tell the user you are done with the refresh for each pivot table in current worksheet</SPAN><br>MsgBox "The worksheet " & ws.Name & " and the pivot table are refreshed."<br><br><SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>