• Pivot Filed Filtering

    From noodnutt@gmail.com@21:1/5 to All on Thu Jan 30 15:41:02 2020
    Hi All

    Using Excel 2013.

    I have the following:

    Dim myPDsht As Worksheet: Set myPDsht = ThisWorkbook.Sheets("Centre Combo") Dim myPT As PivotTable: Set myPT = myPDsht.PivotTables("Centre_Combo")
    Dim myPF As PivotField: Set myPF = myPT.PivotFields("CC")

    This works exactly as expected.
    With myPT
    .PivotFields("CC").ClearAllFilters
    End With

    but!

    If I declare it as :
    With myPT
    .PivotFields(myPF).ClearAllFilters
    End With

    It halts, citing: Unable to get the PivotFields Property of the PivotTable, even though when I mouse over, it shows that myPF="CC"

    Moving forward, Even if I structure it as follows: When I try to include any filtering

    With myPT
    .PivotFields("CC").ClearAllFilters
    .PivotFields("CC").PivotFilters.Add Type:=xlCaptionContains, Value1:="T05"
    end with

    It Errors out with: Object-Define Error..??, I don't get it, I have clearly defined "CC" as my Pivot Field

    It frustrates me no end why it has to be so fickle, I really need some guidance to understand this please.

    TIA
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Thu Jan 30 15:53:40 2020
    I just ran the macro recorder, it returned the following:

    ActiveSheet.PivotTables("Centre_Combo").PivotFields("CC").ClearAllFilters ActiveSheet.PivotTables("Centre_Combo").PivotFields("CC").CurrentPage = "T-05"

    Again, I don't quite understand, if I am already declaring each section:

    Sheet, Pivot & Field Names, why can I not using this declared referencing as opposed to what the recorder spits out.

    Cheers
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)