format numeric data in pivot table

[Richard]

I just created program to make pivot table.

How can I format the 'numbers' part of the pivot table to be comma
delineated with two significant digits after the period?

Note, I'm not even sure how to select just the 'data' part of the pivot table.

 

[Mike H.]

Here is all you do:
right-click inside the pivot-table. Select PivotTable Wizard. Then select
Layout. Then select the Item in the Data area of the resulting layout
screen. Double-click it and then click the Number button. Check the 1,000
separator box (comma).

That is it!

 

[Richard]

Mike,
I need to do this in the macro that created the pivot table.
So I can't right-click inside of the data.

 

[Richard]

Mike,
I need to do this in the macro that created the pivot table.
So I can't right-click inside of the data.

 

[Mike H.]

I don't think you can do it to the pivot table but if you select the columns
that the data resides in and format it within your macro and then add code to
the worksheet calc() function to reselect and reformat each time the pivot
table changes you can accomplish the same thing.

Here is how I did it recently:

'this function goes in the "ThisWorkbook" Objects List in the VBA editor

Public OutletVal As String

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'put code in here to reformat the cells if the value of a "critical cell" in
the pivot-'table, outletval, changes.
if range("outletval").value<>outletval then
'put code here
end if
End Sub


HTH

 

[Richard]

Only problem with you solution is that every time I run my program I will get
a different number of rows.
I guess I cold just format a number of rows larger than I would likely
expect to ever create.

 

[Mike H.]

if you know your pivottable starts on row 5 you could do this:
let x=5
do while true
if cells(x,1).value="Grand Total" then 'pivottable total at bottom row...
exit do
end if
x=x+1
Loop

'now you know your pivot table goes from rows 5 - X.