• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Control Pivot Table Report Filter Using Multiple Cell Values

KyleB

New Member
Hello all,

I'm working on a report that uses a pivot table to display multiple years of daily data for several product prices.

I need to be able to control the report filter to display several different months based on the value of a specific cell. Here is the VBA I have so far.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Intersect(Target, Range("k2:k3")) Is Nothing Then Exit Sub
 
Dim pt As PivotTable
Dim Field As PivotField
Dim ThisMonth As String
 
Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Column1")
ThisMonth = Worksheets("Sheet4").Range("k2").Value
 
With pt
Field.ClearAllFilters
Field.CurrentPage = ThisMonth
pt.RefreshTable
End With
 
End Sub

This code only sorts based on the single value in cell K2. I'd appreciate any and all help.

Thank you!
 
Since K2 contains a string showing the month name, presumably, what other cells do you need to capture with month names? You are wanting to filter on multiple months, correct? Or does the single cell contain multiple month names?
 
Thanks for the reply!

I need to filter on multiple months based on the single cell K2. The trouble I have is in getting the PivotField to use multiple strings.
 
What do the values in L2 look like? Are they separated by commas, or some other delimiter?
 
Hi, KyleB!
Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.
Regards!
 
Narayan,

That's pretty close, thank you, but I'd love it if k2 was a single value with a data validation list.

Here's a sample file. Sorry it took me a while to get this on here.
 

Attachments

  • VBA Tester.xlsm
    105.1 KB · Views: 71
Hi Kyle ,

Thanks for uploading your file ; however , can you clarify what you mean by :
I need to be able to control the report filter to display several different months based on the value of a specific cell.
Your data validation is a list of only single months , unless you plan to extend the DV in K2 to cells K3 , K4 and so on , so that over a range of cells you can have multiple months.

Or do you mean that each time you change the selection in K2 , the pivot table should use this value for the report filter ?

Narayan
 
Narayan,

To answer your question, when I change k2 I need the filter to include the selected month, the prior month and the same month of the previous year. I probably should have been more clear to begin. I'm pretty new at this.

Thanks,

Kyle
 
My report uses a pivot table to display year-over-year and month-over-month price changes. I need a user to be able to select a single month, say March 2014, from a data validation list which would change the pivot filter to include the month selected (March 2014), the previous month (February 2014), and the same month of the previous year (March 2013).

I hope that helps clear things up. Thanks for your patience and continued help.
 
Hi All,

I am struggling to provide Multiple Cell Values to Auto Filter Pivot Table.

Suppose here I want to put Month as well as Name of Employee as two separate cell values to enable Auto Filter Pivot Table (With these two filters) how do I do that?

Pls help!
 
Back
Top