Excel VBA – Pivot table/chart 3 – Data fields control

Switching Data Fields using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

1. Switching Data fields (Quantity, Amount, Average Selling Price, Average Order Value)

Sub DisplayValue()
    
    OptimizeCode_Begin
    
    With ActiveSheet.Shapes("Drop Down 3").ControlFormat
    
        Select Case .ListIndex
            Case Is = 1
                Qty
            Case Is = 2
                Amt
            Case Is = 3
                ASP
            Case Is = 4
                AOV
        End Select
        
    End With
    
    DataLabel
    
    OptimizeCode_End

End Sub

2. Add ‘column’ to Data field.

Sub Amt()

    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "Amt" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi
    
    With pt.PivotFields("Amt")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "$#,##0"
        
    End With
 
End Sub

Sub Qty()
   
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "Qty" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi

    With pt.PivotFields("Qty")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0"
    End With
    
End Sub

Sub ASP()
  
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "ASP" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi
    
    With pt.PivotFields("ASP")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With
    
End Sub

Sub AOV()
   
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "AOV" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi
        
    With pt.PivotFields("AOV")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With

End Sub