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!
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!