• 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.

Auto report filter in pivot table

R

raisc12

New Member
Hi,


I need some help on pivot tables. I have 2 different pivot tables in 2 different worksheets with similar data reference. These 2 both have "Week No." and "Monthly" report filters. If I choose specific value for "Week No.", then the "Monthly" filter should have "(All)" value and vice-versa. It is good to have macro that can do this but my main problem is how to make similar filter value for to different pivot tables. For instance, in PivotTable1, if I have Week No. "37" and Month as "(All)", then I want to automatically choose Week No. "37" in PivotTable2 as well. If I change the filter of PivotTable1 to Week No. "(All)" and Month "August", I want PivotTable2 to have similar filter (Week No. "(All)" and Month "August"). Is there a macro to do this automatically?


Please share. Thanks.
 
Hi raisc12....


If you are open to a macro/VBA solution, I've had success using VBA combined with a simple data validation. My example is slightly different than yours in that I have 2 pivot tables with DIFFERENT data sources. The basic idea should work, though.


In the code below for the Worksheet_Change() event, cell $U$1 has the data validation, which matches the particular pivot field (here called "Geo") that I need to set in my 2 pivot tables. (The data validation for cell $U$1 is a list over to the side somewhere else, that matches the available choices in the pivot table field. I hope that's clear!)


Then, when I change the value in cell $U$1, the pivot fields are changed to match and the pivot tables are changed as well.


Note that you can extend this idea to use more than one data validation cell, if you need to change more than one pivot field.


-------------------------------------------------


Private Sub Worksheet_Change(ByVal Target As Range)


Application.ScreenUpdating = False


If Target.Address = "$U$1" Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("Geo").CurrentPage = _

Target.Value

ActiveSheet.PivotTables("PivotTable2").PivotFields("Geo").CurrentPage = _

Target.Value

End If


Target.Select

Application.ScreenUpdating = True


End Sub


-------------------------------------------------
 
Thanks F106dart. I'm still new in using macro so can you help me modify the arguments. I tried to modify it as far as I can but I can't seem to make the macro run.


My file have 3 worksheets, data source, dashboard, and calculation. In the calculation worksheet, i have one pivot table which displays a chart on the dashboard. I want this pivot to be my reference for the other pivot (located in the dashboard worksheet) to automatically choose filter.


Hope you can help me to modify this. Thanks a lot in advance. :)
 
Any chance you could share a simplified version of what you are working on? (And I can't promise a speedy reply, unfortunately!)
 
In the mean time you might look at this very helpful page from Jon Peltier: http://peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba/
 
You must log in or register to reply here.
Back
Top