Forum Discussion

teckxhenw's avatar
teckxhenw
Copper Contributor
Jan 31, 2023

Filter PivotTable based on Cell Value VBA

Hi all,

I am trying to setup a VBA button to Filter PivotTable based on Cell Value.

Assuming i am trying to filter based on value "2021/Company/doc1"

The below code works if i hardcode the value under "Current Page Name".
___________________________________________________________________________________________________________
Sub FilterPageValue()

Dim PT As PivotTable
Dim PF As PivotField
Dim Str As String

Set PT = Worksheets("Double Entries").PivotTables("PvTDE")
Set PF = PT.PivotFields( _
"[#GL_LineItems].[Accounting Document Number].[Accounting Document Number]")
Str = Worksheets("Double Entries").Range("D3").Value
PF.ClearAllFilters


PF. _
CurrentPageName = "[#GL_LineItems].[Accounting Document Number].&[2021/Company/doc1]"

End Sub

__________________________________________________________________________________________________________________

However, in order to filter based on cell value, From most online tutorials,

the below code should work; but it is not working for me. I am receiving this error.

VBA: Unable to set CurrentPage property of PivotField class" error​


Sub FilterPageValue()

Dim PT As PivotTable
Dim PF As PivotField
Dim Str As String

Set PT = Worksheets("Double Entries").PivotTables("PvTDE")
Set PF = PT.PivotFields( _
"[#GL_LineItems].[Accounting Document Number].[Accounting Document Number]")
Str = Worksheets("Double Entries").Range("D3").Value
PF.ClearAllFilters


PF.CurrentPage = Str

End Sub

_____________________________

How do i rectify this ?


Regards

Wong

No RepliesBe the first to reply

Resources