PivotTable Number Formatting with VBA

Rob71179

New Member
Joined
Sep 29, 2011
Messages
7
Hi,

I am working in Excel 2007, and I have a pivot table with several fields that I want to make data fields, but not at the same time. Essentially, I want to be able to toggle between Revenue, Quantity, and ASP (which is a formula). The problem is that every time I do a number format for Revenue, for example, de-select it as a data field (i.e. Sum of Revenue), and then re-select it I lose my number formatting. To fix this, I am trying to write a VBA macro that will refresh the formatting depending on which field is selected (i.e. Revenue, Quantity, or ASP). Revenue will have the format "$#,##0", Quantity - "#,##0", and ASP "$#,##0.00".

I have written some simple code as a test.

Sub ReTest()
If ActiveSheet.PivotTables("Rev").PivotFields("Revenue").Orientation = DataFields Then
Range("F27").Select
ActiveCell.Formula = "hello"
Range("F28").Select

End If

End Sub

Essentially, I want to write an If-statement that will determine if Revenues are in the data fields, and if so to write the word hello in a cell outside the pivot table. However, when I execute this code, "hello" appears in the specified cell when Revenue, Qunatity, or even no data field is selected leading me to believe that my If-statement is not being read properly. Can you please help?

Thank you!
 
Hi Jerry,
I'd definitely be interested in learning how the code structure could be simplified. Take your time, though, no hurry...

Thanks again,
Rob
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi Jerry,
I'd definitely be interested in learning how the code structure could be simplified. Take your time, though, no hurry...

Thanks again,
Rob
 
Upvote 0
I'd definitely be interested in learning how the code structure could be simplified.

Hi Rob,

If I'm understanding what you are trying do, I think you can simplify structure and get
the same formatting result using an On Error Resume Next statement.

Code:
Sub PivotNumberFormat2()
With ActiveSheet.PivotTables("PivotTable1")
    On Error Resume Next
    If .PivotFields("Sum of Quantity").Orientation = xlDataField Then
        .PivotFields("Sum of Quantity").NumberFormat = "#,##0"
    End If

    If .PivotFields("Sum of ASP").Orientation = xlDataField Then
        .PivotFields("Sum of ASP").NumberFormat = "$#,##0.00"
    End If

    If .PivotFields("Sum of Revenue").Orientation = xlDataField Then
        .PivotFields("Sum of Revenue").NumberFormat = "$#,##0"
    End If

    If .PivotFields("Sum of ASP per Area/Vol").Orientation = xlDataField Then
        .PivotFields("Sum of ASP per Area/Vol").NumberFormat = "$#,##0.0000"
    End If
    
    If .PivotFields("Sum of Total Area/Volume").Orientation = xlDataField Then
        .PivotFields("Sum of Total Area/Volume").NumberFormat = "#,##0"
    End If
End With
End Sub
 
Upvote 0
Rob, Just one more approach to share...

You might consider setting this up so you or another user of your workbook
could change the fields and formats from the worksheet instead of
editing the VBA code.

To do that, you could make a look up table listing the field and its
corresponding format.

Excel Workbook
AB
1FieldFormat
2Quantity#,##0
3ASP$#,##0.00
4Revenue$#,##0
5ASP per Area/Vol$#,##0.0000
6Total Area/Volume#,##0
Pivot Field Formatting


Then use this code to do the same steps as your macro, using the
values in the lookup table.

Code:
Sub PivotNumberFormat_from_Range()
    Dim i As Long
    Dim sItem As String
    Dim varFormats() As Variant
    varFormats = Range("$A$2:B$6") 'or named range
    
    On Error Resume Next
    For i = 1 To UBound(varFormats, 1)
        With ActiveSheet.PivotTables("PivotTable1")
            sItem = "Sum of " & varFormats(i, 1)
            If .PivotFields("Sum of " & sItem).Orientation _
                = xlDataField Then
                    .PivotFields(sItem).NumberFormat = varFormats(i, 2)
            End If
        End With
    Next
End Sub

This is a nice concept and I hope others will benefit from your idea. :)
 
Upvote 0

Forum statistics

Threads
1,224,525
Messages
6,179,317
Members
452,905
Latest member
deadwings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top