excelvbapivot-tablepowerpivotcurrent-page

"Unable to set the CurrentPage property of the PivotField Class" with Pivot Table Filtering Attempt


So I have been trying to figure out what I am doing wrong for the past two days and I can't seem to get past this error.

The error is "Unable to set the CurrentPage property of the PivotField Class"

Here is my code:

Sub FilterBasedOnCellValue()
 Dim wb As Workbook
 Dim ws As Worksheet
 Dim rng As Range
 Dim pt As PivotTable
 Dim PFilter As String
 Dim pf As PivotField
 Dim filterValue As String
 Dim leftp As String
 Dim rightp As String

 Set wb = Application.Workbooks("Activity Report (Automated WIP).xlsm")
 Set ws = wb.Worksheets("Formulas")
 Set pt = ActiveSheet.PivotTables("DailyActivity")
 Set pf = pt.PivotFields("[Activity_RAW].[Column1].[Column1]")
 Set rng = ws.Range("Filter")

 leftp = "&["
 rightp = "]"
 PFilter = "[Activity_RAW].[Column1]." & leftp & rng & "T00:00:00" & rightp

  With pf
   .ClearAllFilters
   .CurrentPage = PFilter
   .RefreshTable
  End With


End Sub

The Line that seems to cause it is the ".CurrentPage = PFilter" line.

The "Column1" filter is in the actual "Filter" field for the pivot table. and when I record a macro of filtering it, it uses CurrentPage, which is why I am confused.

My Pivot Table is an OLAP Based Pivot so I am not certain if I am using the right code for it?

Any help would be appreciated.

Thank you!


Solution

  • So I Found out what was causing the issue!

    I needed to change ".CurrentPage" to ".CurrentPageName"

    I will post it here for anyone that might be running into the same issue that I was!

    Sub FilterBasedOnCellValue()
     Dim wb As Workbook
     Dim ws As Worksheet
     Dim rng As Range
     Dim pt As PivotTable
     Dim PFilter As String
     Dim pf As PivotField
     Dim filterValue As String
     Dim leftp As String
     Dim rightp As String
    
     Set wb = Application.Workbooks("Activity Report (Automated WIP).xlsm")
     Set ws = wb.Worksheets("Formulas")
     Set pt = ActiveSheet.PivotTables("DailyActivity")
     Set pf = pt.PivotFields("[Activity_RAW].[Column1].[Column1]")
     Set rng = ws.Range("Filter")
    
     leftp = "&["
     rightp = "]"
     PFilter = "[Activity_RAW].[Column1]." & leftp & rng & "T00:00:00" & rightp
    
      With pf
       .ClearAllFilters
       .CurrentPageName = PFilter
      End With
     
      pt.RefreshTable
    
    End Sub