CODEWITHSUNDEEP
excelvbapivot-table
Tobi
Tobi

Reputation: 89

How to select the last item in a pivot filter for multiple pivot tables?

I need help with the below code and pivot table.

I run my script on a weekly basis and each time I need time select the last available item in the below pivot tables (PivotTable1, PivotTable2 and PivotTable3):

enter image description here

I tried the below code but it doesn't work:

Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim lCount As Long
Dim lWeeks As Long

On Error Resume Next
lWeeks = 1
If lWeeks = 0 Then Exit Sub

Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable1")

    For Each pi In pt.PivotFields("Week").PivotItems
        pi.Visible = False
    Next pi

With pt.PivotFields("Week")
    For lLoop = .PivotItems.Count To 1 Step -1
        .PivotItems(lLoop).Visible = True
        lCount = lCount + 1
        If lCount = lWeeks Then Exit For
    Next lLoop
End With

On Error GoTo 0
Application.ScreenUpdating = True

I also tried the below but it's still not working:

Sheets("Pivot").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("ExtractDate"). _
    ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("ExtractDate").CurrentPage _
    = ThisWorkbook.Worksheets("Pivot").Range("B2").Value

In this case I'm having the Runtime Error 1004: Unable to get the PivotTables property of the Worksheet class.

Can you please advise how to modify the above codes to select the last available item in the 'Week' filter?

Also, how to modify this code to select the last value for these 3 pivot tables?

Thanks in advance.

Upvotes: 2

Views: 1038

Answers (1)

Asger
Asger

Reputation: 3877

You can set the current filter page with the name of the last (or first) PivotItem:

With ActiveSheet.PivotTables("PivotTable1").PageFields("Week")
    .ClearManualFilter        ' or ClearAllFilters
    .AutoSort xlAscending, .SourceName
    .CurrentPage = .Pivotitems(.Pivotitems.Count).Name
    If .CurrentPage = "(blank)" And .Pivotitems.Count > 1 Then
        .CurrentPage = .Pivotitems(.Pivotitems.Count - 1).Name
    End If
End With

If the last entry is blank, it selects the previous one.
If you need the other end of your date range, just change xlAscending to xlDescending.

You can loop over all PivotTables in a worksheet and set each filter to the last page by this:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
    ' Set pf = pt.PageFields("Week")
    For Each pf In pt.PageFields
        pf.ClearManualFilter        ' or ClearAllFilters
        pf.EnableMultiplePageItems = True
        pf.AutoSort xlAscending, pf.SourceName
        pf.CurrentPage = pf.Pivotitems(pf.Pivotitems.Count).Name
        If pf.CurrentPage = "(blank)" And pf.Pivotitems.Count > 1 Then
            pf.CurrentPage = pf.Pivotitems(pf.Pivotitems.Count - 1).Name
        End If
    Next pf
Next pt

At least 1 item has to remain visible, so you can't loop over all items and set them .Visible = False. A loop over all except the last PivotItem should work, but is too slow.

I added a .RefreshTable to refresh the data in your PivotTable. If there are still wrong informations, you can refresh the PivotCache of your workbook additionally:

Dim pc As PivotCache
For Each pc In ActiveWorkbook.PivotCaches
    pc.MissingItemsLimit = xlMissingItemsNone
    pc.Refresh
Next pc

Upvotes: 1

Related Questions