Get the criteria value in a pivot table custom-filtered row label-CodePudding

The TGL row label in a pivot table is filtered using custom-filter as shown in the image below.

What I need is a vba code to know what is the criteria value which the TGL filtered.
(In this case, the criteria value is "1-Jan-2022"), so I don't need to run a macro with a redundant code like sub test1 below :

Sub test1()
Dim dt As String
dt = Format(DateAdd("m", -2, Format("01/" & Month(Date) & "/" & Year(Date))), "dd-mmmm-yyyy")

With ActiveSheet.PivotTables("ptHarga").PivotFields("TGL")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=dt
End With
  
End Sub

Although it doesn't hurt me, but sub test1 is again and again filter the TGL with the same criteria although the previous condition of the TGL already filtered with that criteria.

So I make a sub which involve IF.... :

Sub test2()
Dim dtNow As Date
dtNow = Format(DateAdd("m", 1, Format("01/" & Month(Date) & "/" & Year(Date))), "dd-mmm-yyyy")

With ActiveSheet.PivotTables("ptHarga").PivotFields("TGL")
    For Each pit In .PivotItems
        If DateDiff("m", CDate(pit), dtNow) = 3 Then Call test1: Exit For
    Next
End With
  
End Sub

In sub test2, the dtNow is used just to assumed that the macro is run in April 2022, if the month difference is 3 from any of the TGL pivot item, then it's time to change the criteria to "1-Feb-2022" (call test1).

I've tried also another sub by using application.min, no loop.

Sub test3()
Dim dtNow As Date
dtNow = Format(DateAdd("m", 1, Format("01/" & Month(Date) & "/" & Year(Date))), "dd-mmm-yyyy")

With ActiveSheet.PivotTables("ptHarga").PivotFields("TGL")
    .ClearAllFilters
    If DateDiff("m", CDate(Application.Min(.DataRange)), dtNow) = 3 Then Call test1
End With
  
End Sub

Out of my curiosity, my question :
Is there a code which can directly get the criteria value in the current filtered TGL of the pivot table field ?

Any kind of respond would be greatly appreciated.
Thank you in advanced.

CodePudding user response：

For example:

Dim pt As PivotTable, pf As PivotField

Set pt = ActiveSheet.PivotTables(1)
With pt.PivotFields("Date").PivotFilters(1)
    Debug.Print "Type", .FilterType
    Debug.Print "Value1", .Value1
End With

See: https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfilter