Change value filter in PIVOT via VBA

[MSerup]

Hi,

I hope someone can help me, regarding change value fields in a pivot using VBA coding. I total I have 4 different fields that I want to users to be able to switch between, Units, Value in EUR, Value in USD and Value in DKK via dedicated buttons. Now here is the problem. Regardless of which of the field are currently in the pivot it should always change to the selected field. I am thinking that an OR function would be able to do the trick but cannot get it to work. Below are the VBAs for changing each of the fields to Units.


Sub To_DKK()

To_DKK Macro


If ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales Units").Orientation = xlHidden Or _
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales EUR Incl. VAT").Orientation = xlHidden Or _
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales USD Incl. VAT").Orientation = xlHidden Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sales DKK Incl. VAT"), "Sum of Sales DKK Incl. VAT" _
, xlSum
Else
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sum of Sales DKK Incl. VAT")
End If

End Sub

[xlnitwit]

Hi,

If you only have one data field at a time, you might simply use 4 macros like this

Please Login or Register  to view this content.