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

Apply filter to all pivot tables in a sheet via macro.

D

Dee

Member
Hi,

Is there a way to update the filter to other pivot tables same as that of first pivot table using macros?

I have 4 pivot tables in a sheet and i am applying filter to first report filter and i want same thing should be updated to all pivot tables via macros.

can Macro experts help me on this?


Thanking you in advance,

Dee
 
There's many variables when dealing with PivotTables, so this probably won't work exactly as is. Hopefully you can make some modifications as needed.

[pre]
Code: 
Sub ChangeAllPivots()

Dim PT As PivotTable
Dim xCriteria

'Criteria is based on current active cell
xCriteria = ActiveCell

For Each PT In ActiveSheet.PivotTables
'you'll need to change the name of the field as appropriate
For Each IT In PT.PivotFields("Name").PivotItems
With PT.PivotFields("Name")
If IT <> xCriteria Then
.PivotItems(IT.Name).Visible = False
End If
End With
Next IT
Next PT

End Sub
[/pre]
 
Hi Luke,


Thank you very much for the solutions.

I have a issue here... The above code is removing the filter from report filter and is showing all the values in the pivot table. What i am looking at is, i am changing the filters of pivot table1 and refreshing the pivot table 1. when i run the macro the same filter should be applied to all the pivot tables in that worksheet.

I tried to edit the macro but was not successful.


Can you please help me on this?


Dee
 
I'm afraid there's no "filter setting" that can be detected from one PivotTable (PT) and applied to the other PT's. =(
 
Hi Dee ,


Do you think this may help ?


http://stackoverflow.com/questions/1423753/excel-2007-filtering-across-multiple-pivot-tables


Narayan
 
Here is code I use (appropriated from some other source I have sadly forgotten):

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "PageFilterNameHere"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B3").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
[/pre]

The way this code works is that I have a control cell in cell B3 on the first worksheet which is linked to the Page Filter on multiple pivot tables throughout the workbook. I use a dropdown box in the control cell that mirrors the options in the page filters. It can be a bit slow if you have large pivot tables, but it works really well.


Two points:


1. The page filter field must be exactly the same in all pivot tables that you want controlled.


2. If you use a different page filter field in a pivot table, it won't be affected by the code.


Hope this helps.
 
Looks like Debra tackled this problem over the holidays:

http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/
 
Katieg,


First, welcome to the forums.

Second, you should read the forum rules located in the Sticky at the top of the thread. Hijacking threads is generally frowned upon. You should create a new thread (and maybe include a link to this one, if you want to reference)


Finally, simply stating "it doesn't work" is not very helpful. You'll need to clearly explain what isn't working (get no answer, get wrong answer, get different answer, etc)
 
Hi Guys,


Just had this same problem, selecting a month filter in 3 diferent pivots.


I used a combo box method to select the month, then the link cell wil give me a number, next to the number I used a vlookup to get the correct month back.


Then used this macro to make the pivots filters move:


----------


Sub pivot_mes()

'

'

'

Dim x As String


'prod_comer, prod_corp, prod_pref are the names of my pivots

'mesINICIO is the filter i wanted to move

'"LISTAS" is the name of the sheet where the result of the vlookup is and D2 is the cell that will have the month


'here the macro goes to where the pivots are


Sheets("PIVOTES1").Select

ActiveSheet.PivotTables("prod_comer").PivotFields("mesINICIO").ClearAllFilters

ActiveSheet.PivotTables("prod_comer").PivotFields("mesINICIO").CurrentPage = Worksheets("LISTAS").Range("d2").Value

ActiveSheet.PivotTables("prod_corp").PivotFields("mesINICIO").ClearAllFilters

ActiveSheet.PivotTables("prod_corp").PivotFields("mesINICIO").CurrentPage = _

Worksheets("LISTAS").Range("d2").Value

ActiveWindow.SmallScroll Down:=9

ActiveSheet.PivotTables("prod_pref").PivotFields("mesINICIO").ClearAllFilters

ActiveSheet.PivotTables("prod_pref").PivotFields("mesINICIO").CurrentPage = _

Worksheets("LISTAS").Range("d2").Value

ActiveWindow.SmallScroll Down:=12


'here the macro comes back to my dashboard sheet

Sheets("DASH").Select

End Sub


-----------


I'm still very new at this so ther's probably a better or shorter way to do this, but this works.
 
Before heading to a VBA solution, it's worth noting that if you have Excel 2010 or later, and if your pivots all come from the same data source (and hence share the same pivotcache) then simply setting up slicers will ensure that the pivots will remain in sync.

Also, note that I rewrote the code that Deb at Contextures has at http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/ so that it is much more efficient, by using a Dictionary to store the settings in the master pivot, and applying those to each slave pivot in turn.

And then I added a routine that checks the version of Excel, and if 2010 or later, uses slicers to sync any pivots that share the same cache, and the dictionary approach for any others.
See http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618
 
You must log in or register to reply here.
Back
Top