Pivottable' s content format

Question

everyone,

In this pivottable I'm working on, the source data has money values and quantities. I have a filter in my pivottable in order to choose what type of data I want to see in my pivottable, whether is "V" or "Q".

My problem is that I need to format the contents of the pivottable accordingly to the type of information I'm seeing: for money values, I need to see the contents in "# ###.00 €" format; for quantity values, in "#,##0".

I already tried to use a DataRange property for the pivotfields (as you can see in the code) and even tried using NumberFormat directly on the pivotfields, but none of these worked.

Do you suggest anything to solve this?

Dim vp As Workbook
Dim type_qv As String
Dim chart_pt As PivotTable
Dim pf As PivotField

Set vp = ThisWorkbook
Set chart_pt = vp.Sheets("Chart").PivotTables("VP_table")

        'this detects what type of data is being used
type_qv = vp.Sheets("Chart").Range("B2").Value

If type_qv = "Q" Then
    For Each pf In chart_pt.RowFields
        pf.DataRange.NumberFormat = "#,##0"
    Next
End If

If type_qv = "V" Then
    For Each pf In chart_pt.RowFields
        pf.DataRange.NumberFormat = "# ###.00 €"
    Next
End If

Untested, but you may be able to use Conditional Formatting to do this. — BigBen
I wouldn't loop, I'd just use: chart_pt.PivotFields("YourPivotField").NumberFormat = "#.##0,00 €" — Damian
Hi, @BigBen, I would rather have this via VBA, because it's less prone to human error. The pivottable changes a lot, may have lots of variation in number of columns and/or rows. — SSGrace
@SSGrace you can Dim PvtField As PivotField and loop through all the pivotfields on your pivot table... — Damian
@SSGrace You look at B2 cell's value to define which value your pivot is displaying ("Sum of Q"). If almost literally anything (tabular view, moving the pivot, showing more than one value etc.) happens, it won't work. In my answer I loop through all shown datafields. — user3819867

user3819867 user3819867 · Accepted Answer · 2019-11-05T13:41:39

It's "Sum of Q" but you don't want to look at the description because they can change it and they can move the table and they can show both fields.

Dim chart_pt As PivotTable, df As PivotField
Set chart_pt = ActiveSheet.Range("A3").PivotTable
For Each df In chart_pt.DataFields
    Select Case True
        Case df.Name Like "*Q"
            df.DataRange.NumberFormat = "#,##0"
        Case df.Name Like "*V"
            df.DataRange.NumberFormat = "# ###.00 €"
        Case Else
            df.DataRange.NumberFormat = "# ###.00"
    End Select
Next

Pivottable' s content format

1 Answers