VBA to select last 12 items in a pivot table drop down

[nguerra]

Looking for any kind of help that can help me solve this problem.

I receive a pivot table once a month. One of the filters has the options of:
yr17mth08,yr17mth09,yr17mth10,yr17mth11,yrmth12,
yr18mth01,yr18mth02,yr18mth03,yr18mth04,yr18mth05,yr18mth06,yt18mth07,yr17mth08,yr18mth09,yr18mth10,yr18mth11,yr18mth12,
yr19mth01,yr19mth02,yr19mth03,yr19mth04,yr19mth05,yr19mth06,yt19mth07.
however, every month the oldest field drops off and a new field is added. It's basically a rolling 24 months of data. The report generated monthly is only looking for the last 12 months. It's important to know that the values in the pivot table are not formatted as dates. I believe they are just strings.

I'm looking for a piece of VBA that can always select the last 12 items in a drop down no matter what they are.

 

[pbornemeier]

Edit first Const line to contain the name of the pivotfield you want to filter.

Option Explicit

    Sub SubSelectLastXPTItems()
    'https://www.mrexcel.com/forum/excel-questions/1106946-vba-select-last-12-items-pivot-table-drop-down.html

    Const sPTField As String = "Year"
    Const lSelectCount As Long = 12
    
    Dim pt As PivotTable
    Dim ptf As PivotField
    Dim bFoundPTF As Boolean
    Dim pti As PivotItem
    Dim lPTICount As Long
    Dim lPTIIndex As Long
    
    'Make sure sPTField is a Pivot Field in the active PT
    With ActiveSheet.PivotTables(1)
        For Each ptf In .PivotFields
            If ptf.Name = sPTField Then
                bFoundPTF = True
                Exit For
            End If
        Next
        If bFoundPTF Then
            Set ptf = .PivotFields(sPTField)
            'How many items in that field?
            For Each pti In ptf.PivotItems
                lPTICount = lPTICount + 1
            Next
            ptf.ClearAllFilters
            If lPTICount > lSelectCount Then
                For lPTIIndex = 1 To lPTICount
                    If lPTICount - lPTIIndex >= lSelectCount Then
                        ptf.PivotItems(lPTIIndex).Visible = False
                    Else
                        ptf.PivotItems(lPTIIndex).Visible = True
                    End If
                Next
            Else
                MsgBox "There " & lSelectCount & " or fewer items in the " & sPTField & " pivotfield.  All will be displayed"
            End If
        Else
            MsgBox sPTField & " is not a pivotfield in the active pivottable."
        End If
    End With
End Sub

 

[nguerra]

Thank you for the reply. The first Const in my case is:

Const sPTField As String = "[CubeData].[Month].[Month]"

When I run the macro I get the following:

Run-time error '1004':
Unable to set the Visable property of the PivotItem class

ptf.PivotItems(lPTIIndex).Visible = False

 

[DanteAmor]

You can put an image or paste your table data here, to see how your pivot table is.

Upload an excel image:
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[pbornemeier]

If it was a regular pivot table "Month" would do. I am not sure about the CubeData aspect of it.