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 Values_to_Units()
'
' Values_to_Units Macro
'
'
If (ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales DKK Incl. VAT"). _
Orientation = xlHidden Or ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales USD Incl. VAT"). _
Orientation = xlHidden Or ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales EUR Incl. VAT"). _
Orientation = xlHidden) Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sum of Sales Units"), "Sum of Sales Units" _
, xlSum
End Sub
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 Values_to_Units()
'
' Values_to_Units Macro
'
'
If (ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales DKK Incl. VAT"). _
Orientation = xlHidden Or ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales USD Incl. VAT"). _
Orientation = xlHidden Or ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales EUR Incl. VAT"). _
Orientation = xlHidden) Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sum of Sales Units"), "Sum of Sales Units" _
, xlSum
End Sub