Category: excel

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