CODEWITHSUNDEEP
excelvbapivot-tableexpand
ms_queen
ms_queen

Reputation: 81

Expand/Collapse First Field Value in PivotTable using VBA

I am trying to get a code to only expand the first value of the row fields, no matter what it is.

Public Sub PivotTable_Collapse()

    Dim pT As PivotTable
    Dim pF As PivotField
    Dim rw As Range

    With ActiveSheet.PivotTables("PivotTable2").RowRange
    Set rw = .Rows(3)
    
    Set pT = ActiveSheet.PivotTables("PivotTable2")
   
    With pT
        For Each pF In pT.RowFields
            pF.DrillTo pF.rw 'I think this is where I need help
            
            MsgBox rw
        Next pF
    End With
    End With

enter image description here

This worked for me! Here is the resolution below.

Sub ExpandFirstSchool()
'
   Dim rw As Range
     With ActiveSheet.PivotTables("PivotTable2").RowRange
        Set rw = .Rows(3)
     End With
     rw.Cells(rw.Cells.Count).Select

     rw.Cells(rw.Cells.Count).ShowDetail = True

End Sub

Upvotes: 0

Views: 572

Answers (1)

Tim Williams
Tim Williams

Reputation: 166256

This worked for me (assuming I understood what you want to do)

Set pt = ActiveSheet.PivotTables(1)
      
With pt.PivotFields("Col001").PivotItems(1)
    'you can use one of these....
    .ShowDetail = True 'expand one level down
    .DrillTo "Col002"  'expand one level using field name
    .DrillTo "Col003"  'expand two levels using field name
End With

My test data row fields are named "Col001", "Col002", "Col003"

Upvotes: 1

Related Questions