Excel VBA – Pivot table/chart 2 – Column fields control

Switching Column Fields using VBA code

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

1. Switching column fields

Sub GroupBy()
   
    
    With ActiveSheet.Shapes("Drop Down 2").ControlFormat
    
        Select Case .ListIndex
            Case Is = 1
                PrTypeView
            Case Is = 2
                ProductView
            Case Is = 3
                ChTypeView
            Case Is = 4
                ChannelView
        End Select
        
    End With


End Sub

2. Add ‘column’ to Column field. Note that ‘ClearColumn’ sub is at the first of the line before adding a ‘column’ to Column field.

Sub PrTypeView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Product_Type")
        .Orientation = xlColumnField
        .Position = 1
    End With
    

End Sub

Sub ProductView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Product_Name")
        .Orientation = xlColumnField
        .Position = 1
    End With

End Sub
Sub ChTypeView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Channel_Type")
        .Orientation = xlColumnField
        .Position = 1
    End With

End Sub


Sub ChannelView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Channel")
        .Orientation = xlColumnField
        .Position = 1
    End With

End Sub

3. Clear Column field

Sub ClearColumn()
    
    Dim pt As PivotTable
    Dim fld As Object
    Set pt = ActiveSheet.PivotTables(1)
    For Each fld In pt.ColumnFields
        Debug.Print fld.Name
        fld.Orientation = xlHidden
    Next fld

End Sub