How to show most recent date in Pivot Table filter field ?

[AngelicaWan]

I just encounter this issue and want to find a solution to auto filter just the most recent date in Pivot Table via VBA.

Like I want to set "TIME" column as filter field in Pivot Table. I searched online and modified a little bit to see whether it could work. Here are the codes:

Module1:
Sub MaxDatePivot()
Dim pfiPivFldItem As PivotItem
Dim PvtTbl As PivotTable
Dim dtmDate As Date
With ThisWorkbook.Sheets("Sheet1").PivotTables(1)
.ClearAllFilters
With .RowRange
dtmDate = Evaluate("MAX(IF(ISNUMBER(" & .Address(0, 0) & ")," & .Address(0, 0) & ",))")
End With
For Each pfiPivFldItem In .PivotFields("TIME").PivotItems
If pfiPivFldItem.Value = "(blank)" Then
pfiPivFldItem.Visible = False
Else
pfiPivFldItem.Visible = CDate(pfiPivFldItem.Value) = CLng(dtmDate)
End If
Next pfiPivFldItem
PvtTbl.PivotFields("TIME").PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=pfiPivFldItem
End With
End Sub


ThisWorkbook:
Private Sub Workbook_Open()
MaxDatePivot
End Sub


However, it showed me the red part is "Type Mismatch".

Is it possible to make it happen? How to modify or write codes to realize it? Thanks!

 

[James006]

Hi,

You could test ...

With ActiveSheet.PivotTables("PivotTable1")
      On Error Resume Next
        .RefreshTable
        .PivotFields("TIME").AutoSort xlDescending, "TIME"
      On Error GoTo 0
    End With

HTH

 

[AngelicaWan]

Hi James,

Thank you so much! You give me an great idea to show other side of vision to solve this problem. And I modified a little bit and it finally works! Good news in this morning!

I put my code below:

With ActiveSheet.PivotTables("PivotTable1")
      On Error Resume Next
        .RefreshTable
        .PivotFields("TIME").AutoSort xlDescending, "TIME"
      On Error GoTo 0
    For i = 1 To .PivotFields("TIME").PivotItems.Count
        If .PivotFields("TIME").PivotItems(i) <> "True" And _
           .PivotFields("TIME").PivotItems(i) <> "False" And _
           .PivotFields("TIME").PivotItems(i) <> "(blank)" Then
            If (.PivotFields("TIME").PivotItems(1) > .PivotFields("TIME").PivotItems(i + 1)) Then
                .PivotFields("TIME").PivotItems(1).Visible = True
                .PivotFields("TIME").CurrentPage = .PivotFields("TIME").PivotItems(1).Value
            End If
        End If
    Next
End With

Happy Friday!

Hi,

You could test ...

With ActiveSheet.PivotTables("PivotTable1")
      On Error Resume Next
        .RefreshTable
        .PivotFields("TIME").AutoSort xlDescending, "TIME"
      On Error GoTo 0
    End With

HTH

 

[James006]

Hi Angelica,

Glad you could fix your problem ...

Thanks ... for your thanks ...