Author: Danny Yun

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

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

Switching Row Fields using VBA code

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

1. Switching row fields

Sub RollUpTimeBy()

    With ActiveSheet.Shapes("Drop Down 1").ControlFormat
        Select Case .ListIndex
            Case Is = 1
                YearView
            Case Is = 2
                MonthView
            Case Is = 3
                WeekView
            Case Is = 4
                DayView
        End Select
    End With
    
End Sub

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

Sub YearView()
    
    ClearRow
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Year")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

Sub MonthView()
    
    ClearRow
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Month")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

Sub WeekView()
    
    ClearRow
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Week")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

Sub DayView()
    
    ClearRow
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

3. Clear Row field

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

End Sub