+ Reply to Thread
Results 1 to 2 of 2

Select Multiple Pivot Field Items in VBA

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Select Multiple Pivot Field Items in VBA

    Hi, i'm a novice with VBA but am trying to get it to cycle through my pivot tables and select three items on a particular filter.

    The filter is "Role" and the pivots contain info regarding multiple roles - i want a macro to change all the pivots to show only 3 specific roles. The below code works, but the filters on the pivots only have one role selected ("PSSR")

    Any ideas how i can adjust my code to select all 3 items and nothing else? Thanks

    Dim PT As PivotTable
    Dim PF As PivotField
    Dim PI As PivotItem
    
    Application.EnableEvents = False
    Application.ScreenUpdating = True
    
    Sheets("Pivots").Activate
        For Each PT In ActiveSheet.PivotTables
          For Each PF In PT.PivotFields
           If PF = "Role" Then
           PF.EnableMultiplePageItems = True
            For Each PI In PF.PivotItems
                If Not PI Like "PSSR" Or PI Like "CAM" Or PI Like "ISR" Then
                   PI.Visible = False
                   Else
                   PI.Visible = True
                   End If
                Next PI
            Else
            End If
               Next PF
           Next PT

  2. #2
    Registered User
    Join Date
    10-10-2011
    Location
    Tyler, TX
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    2

    Re: Select Multiple Pivot Field Items in VBA

    Depending on the name of your pivot table, this code should work for what you're looking for.

        For Each PT In ActiveWorkbook.ActiveSheet.PivotTables
            Select Case PT.Name
            Case "PivotTable1"
                For Each PF In PT.PivotFields
                    Select Case PF.Name
                    Case "Content Type"
                        For Each PI In PF.PivotItems
                            Select Case PI.Name
                            Case _
                            "PSSR", _
                            "CAM", _
                            "ISR", _
                                PI.Visible = True
                            Case Else
                                PI.Visible = False
                            End Select
                        Next
                    End Select
                Next
            End Select
        Next

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1