2 pivots with same filtering vba

[konijay]

Hello experts...


I've been using a code that changes the filtering on multiple pivots when one is changed and it worked great. But I need to move the report filter = DEPARTMENT down to row labels. so the code no longer works. Could you guys help me with VBA code please?...


I have 2 pivots(2 different data sources) on one worksheet.

Both contain same DEPARTMENTS. The filtering(on DEPARTMENT) on both pivots should mirror each other.


thank you

 

[Luke M]

Can you post the code you are using? I'm guessing there's a line about row/column fields that needs tweaked.

 

[konijay]

Hi,Luke

I know below code(from contextures.com) looks thru all worksheets and stuff but...

[pre]

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean

On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False

Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[/pre]

 

[Luke M]

Thanks konijay. And, in all PTs, you're moving the DEPARTMENT filter down to rows? Is the error that code doesn't work (no error msg, but didn't do what you want), or it crashes?

 

[konijay]

Correct. I am moving DEPARTMENT(which used to be the report filter) to Row Labels for both pivots.


There is no error/crash from VBA...it just doesn't change the filtering for both.


thanks

 

[Luke M]

Have you been able to more the DEPARTMENT field in all the PT's yet, or is that where you're currently stuck? If the macro crashed in an earlier point when you were trying to move things, the Change_events macros might have got disabled w/o being reactivated. If that's the case, need to run this script:

[pre]

Sub TurnOn()
Application.EnableEvents = True
End Sub

[/pre]

 

[konijay]

DEPARTMENT is already moved to Row Labels and there is no crash...the code just doesn't work. I am assuming the code is looking for REPORT FILTER, not row labels?...

 

[Luke M]

Do you know if the code is running or not?

The macro from contextures doesn't look at specific fields, it just looks through them all and sets all the fields to the same filtering.

 

[konijay]

I even tried on the sample file from contextures.


I moved Report Filter field down to Row labels and it did not work...isn't the code looking at the fields that are under Report Filter?..

 

[Luke M]

Ah, I see that you are correct. For future readers, the contextures article is:

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


@konijay

From the article, it appears that it only works on Report Filters. Since you've moved it down to row labels, it's not getting picked up.

Can you add the field to both locations (Report Filter and Row item)?

 

[konijay]

Pivot doesn't let you add twice...