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!
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