VBA to change pivot table rows and columns

[Christof]

Hi,
I was wondering if it's possible via the use of VBA to change rows and columns on a pivot table.


So on the basic example above (and attached), if i change cell d19 to "Region", the pivot table would display the rows as regions rather than country.
Likewise, if I change d21 to "Revenue", it will change the sum column.

Is this possible, or am I dreaming?

I know how to select filters on pivots using VBA, but don't know how to change rows and columns.

Many thanks
Chris

 

[Debaser]

Right-click the worksheet tab, choose View Code and then add this:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo clean_up
    If Not Intersect(Target, Range("D19")) Is Nothing Then
        If Len(Range("D19").Value) <> 0 Then
            Application.EnableEvents = False
            With PivotTables(1)
                .RowFields(1).Orientation = xlHidden
                .PivotFields(Range("D19").Value).Orientation = xlRowField
            End With
        End If
    ElseIf Not Intersect(Target, Range("D21")) Is Nothing Then
        If Len(Range("D21").Value) <> 0 Then
            Application.EnableEvents = False
            With PivotTables(1)
                .DataFields(1).Orientation = xlHidden
                .PivotFields(Range("D21").Value).Orientation = xlDataField
            End With
        End If
    End If
   
clean_up:
    Application.EnableEvents = True
End Sub

 

[Christof]

Thank you so much!!
#hearteyes

 

[Debaser]

You're welcome.

 

[Christof]

Hi, sorry me again...

So i guess i thought I was clever enough that I would have been able to work that into my code, but I can't seem to get it working.
Can I be cheeky and ask for help again?

So what I need on my Pivot table (Dim = pt), is the Row field to be either "Calendar Year" or "Financial Year" (selected from cell H3).

And the sums to be either "Pax" or "Revenue" (selected from cell C3)

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim KeyCells As Range
    Set KeyCells = Range("C3:M5")
       
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Application.Calculation = xlManual
        Application.StatusBar = "Hi " & Application.UserName & "! Your report will be ready in a few seconds..."

       
        'Set the Variables to be used
        Dim pt As PivotTable
        Dim Field As PivotField
        Dim Field2 As PivotField
        Dim Field3 As PivotField
        Dim Field4 As PivotField
        Dim NewCat As String
        Dim pi As PivotItem
        Dim Yeartype As String


        Yeartype = Range("h3").Value
       
        'Here you amend to suit your data
        Set pt = Worksheets("Brand Summary").PivotTables("PivotTable1")
        Set Field = pt.PivotFields("Brand")



        If Yeartype = "Financial Year" Then
            Set Field4 = pt.PivotFields("Financial Year2")
        Else
            Set Field4 = pt.PivotFields("Calendar Year2")
        End If




        pt.AllowMultipleFilters = True


        NewCat = Worksheets("Brand Summary").Range("e5").Value
        NewCat2 = Worksheets("Brand Summary").Range("g3").Value
        NewCat3 = Worksheets("Brand Summary").Range("g3").Value - 1
        NewCat4 = Worksheets("Brand Summary").Range("h3").Value


       
        pt.ClearAllFilters
       
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
       

        For Each pi In Field4.PivotItems
            If Not ((pi.Name = NewCat2) Or (pi.Name = NewCat3)) Then
                pi.Visible = False
            End If
        Next
        pt.RefreshTable


        Application.Calculation = xlAutomatic
        Application.StatusBar = "All done!"
   End If


End Sub

 

[Debaser]

Best guess:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim KeyCells As Range
    Set KeyCells = Range("C3:M5")
       
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Application.Calculation = xlManual
        Application.StatusBar = "Hi " & Application.UserName & "! Your report will be ready in a few seconds..."
       
        'Set the Variables to be used
       Dim pt As PivotTable
        Dim Field As PivotField
        Dim Field4 As PivotField
        Dim NewCat As String, NewCat2 As String, NewCat3 As String
        Dim pi As PivotItem
        Dim Yeartype As String, sumField As String

        Yeartype = Range("h3").Value
       
        'Here you amend to suit your data
       Set pt = PivotTables("PivotTable1")
        Set Field = pt.PivotFields("Brand")
        pt.ManualUpdate = True

        sumField = Range("H3").Value
        pt.RowFields(1).Orientation = xlHidden
        If Yeartype = "Financial Year" Then
            pt.PivotFields("Financial Year2").Orientation = xlRowField
        Else
            pt.PivotFields("Calendar Year2").Orientation = xlRowField
        End If
        Set Field4 = pt.RowFields(1)
        pt.DataFields(1).Orientation = xlHidden
        pt.PivotFields(sumField).Orientation = xlDataField

        pt.AllowMultipleFilters = True

        NewCat = Range("e5").Value
        NewCat2 = Range("g3").Value
        NewCat3 = Range("g3").Value - 1

        pt.ClearAllFilters
       
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
       
        For Each pi In Field4.PivotItems
            If Not ((pi.Name = NewCat2) Or (pi.Name = NewCat3)) Then
                pi.Visible = False
            End If
        Next
        pt.ManualUpdate = False
        pt.RefreshTable

        Application.Calculation = xlAutomatic
        Application.StatusBar = "All done!"
   End If

End Sub

 

[Christof]

Thanks, it gives me the following error message..

 

[Debaser]

Not really surprising since your real pivot table layout bears no relation to the file you posted originally!

I think you want this:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim KeyCells As Range
    Set KeyCells = Range("C3:M5")
       
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Application.Calculation = xlManual
        Application.StatusBar = "Hi " & Application.UserName & "! Your report will be ready in a few seconds..."
       
        'Set the Variables to be used
      Dim pt As PivotTable
        Dim Field As PivotField
        Dim Field4 As PivotField
        Dim NewCat As String, NewCat2 As String, NewCat3 As String
        Dim pi As PivotItem
        Dim Yeartype As String, sumField As String

        Yeartype = Range("h3").Value
       
        'Here you amend to suit your data
      Set pt = PivotTables("PivotTable1")
        Set Field = pt.PivotFields("Brand")
        pt.ManualUpdate = True

        sumField = Range("H3").Value
        If Yeartype = "Financial Year" Then
            Set Field4 = pt.PivotFields("Financial Year2")
        Else
            Set Field4 = pt.PivotFields("Calendar Year2")
        End If
        pt.DataFields(1).Orientation = xlHidden
        pt.PivotFields(sumField).Orientation = xlDataField

        pt.AllowMultipleFilters = True

        NewCat = Range("e5").Value
        NewCat2 = Range("g3").Value
        NewCat3 = Range("g3").Value - 1

        pt.ClearAllFilters
       
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
       
        For Each pi In Field4.PivotItems
            If Not ((pi.Name = NewCat2) Or (pi.Name = NewCat3)) Then
                pi.Visible = False
            End If
        Next
        pt.ManualUpdate = False
        pt.RefreshTable

        Application.Calculation = xlAutomatic
        Application.StatusBar = "All done!"
   End If

End Sub

 

[Christof]

Yeah I made the error of assuming that I would be able to slot the simple one into my more complex version!

MiiiiiiiiiiiiiiiiissstAAAAAAAAAAAAAAAAAAAAAAAAkkkkkkeee


Thanks again for your awesome help today!!
All working now

 

[Debaser]

You're welcome. Again.