Filter PivotTable based on Cell Value VBA

[teckxhenw]

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 the code?
Regards
Dan

 

[Alex Blakenburg]

What do you have in D3 ?
See if creating the Str as in the below works for you.

How about:

    ' Assuming you need this
    ' "[#GL_LineItems].[Accounting Document Number].&[2021/Company/doc1]"
    ' and in D3 you have this "2021/Company/doc1"
    Str = "[#GL_LineItems].[Accounting Document Number].&[" & _
            Worksheets("Double Entries").Range("D3").Value & _
            "]"
               
    PF.CurrentPage = Str

 

[teckxhenw]

D3 is the value that i am trying to filter. A sample of the value that i would like to filter is as follows:

2021/SG20/2800000246

I have changed the code to belowP

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 = "[#GL_LineItems].[Accounting Document Number].&[" & _
Worksheets("Double Entries").Range("D3").Value & _
"]"
PF.ClearAllFilters

PF.CurrentPage = Str


End Sub

___________________

I am still receiving this error "

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

Regards

 

[Alex Blakenburg]

Can you give this a try:

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 = "[#GL_LineItems].[Accounting Document Number].&[" & _
        Worksheets("Double Entries").Range("D3").Value & _
        "]"
        
PF.ClearAllFilters

PF.VisibleItemsList = Array(Str)

End Sub

 

[teckxhenw]

Hi, now i receive Run-time error '1004'. Application-defined or object-defined error.

 

[Alex Blakenburg]

Unless I am mistaken, your pivot is a Power Pivot Table and you have loaded the Table into the Data Model and the Table name is "#GL_LineItems".
Can you get rid of the "#" in the table name ? I can't even create a Table with a name starting with "#". If you change the Table Name and also change it to match in the code, does that fix the issue ?
If that doesn't fix it can you give me an XL2BB with a couple of rows of Data ?
Also please confirm the Table Name.
PS: You also didn't mention which line of code caused the error.

Xl2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.