VBA, filtering pivot table based on cell value

[sanantonio]

Sub FilterPivot_SingleCell()
Dim PV_WS As Worksheet
Dim Pv_Table As PivotTable
Dim Pv_Field As PivotField
Dim Fltr_KW As String
'Assigning Worksheet of Pivot Table
Set PV_WS = Worksheets("Report")
'Assigning PivotTable Name
Set Pv_Table = PV_WS.PivotTables("PivotTable12")
'Assigning PivotTable Field to Filter
Set Pv_Field = Pv_Table.PivotFields("Short")
'Assigning Cell Value to the Filter KeyWord
Fltr_KW = Range("B3").Value
'Clearing all filters
Pv_Field.ClearAllFilters
'Setting Filters to the cell value
Pv_Field.CurrentPage = Fltr_KW
End Sub

My pivot table name is correct, and it's on the worksheet called "Report", the filter is called "Short" and there is a value in B3.

I've had the code in "This Workbook", in the "Report" worksheet and in a module and it just doesn't seem to work.

I've had a similar macro to this working some years ago but I can't find my version so this has come from a quick google but it doesn't seem to work.

Any ideas?

 

[DanteAmor]

Works for me.

You could upload a copy of your file or just "Report" sheet to a free site such www.dropbox.com or google drive.
Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
If the workbook contains confidential information, you could replace it with generic data.

 

[sanantonio]

Works for me.

You could upload a copy of your file or just "Report" sheet to a free site such www.dropbox.com or google drive.
Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
If the workbook contains confidential information, you could replace it with generic data.

That would be great please, I'm sure I'm just missing something simple. Particularly frustrating when I've had this macro working in the past!

Loading Google Sheets

Is a link to a workbook with rubbish data with the macro in and I still can't get it to function. Any help would be appreciated.

I don't have much confidence in the above link working as it doesn't work for me but it's all google drive will give me :-/

 

[Alex Blakenburg]

I am having the same issue as Dante, that code works for me. If you are going to give sample data you really need to give representative data.
Your original data has merged cells which are not in the sample you provided also you have different sheet names, addresses & pivot table name.

Did you try running the code on the data you sent us ?
Does it really not work even on that sample data ?

When you say doesn't work, what does that mean ?
Are you getting an error message, if so what is it and when you press debug which line is highlighted ?
As it stands if the filter value doesn't exist you will get an 1004 error

Try adding the debug.print line as shown below and see if it picking up your filter value.
(view in the immediate window, Ctrl+G if you can't see the window)

Debug.Print Fltr_KW
'Setting Filters to the cell value
Pv_Field.CurrentPage = Fltr_KW