Re: Change Multiple Page Fields Simultaneously in Pivot Tables

I have a question in regards to a previous post. The post title is the same as above. I was able to get the original code to work but not if I selected Multiple Items from the drop-down field it would not pass this onto the other pivot tables. If I selected All or just 1 field from drop-down then it worked on all pivots. I used this code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim ws As Worksheet
Dim wsMain As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pi As PivotItem
Dim pf As PivotField

On Error Resume Next
Set wsMain = Sheets("Changebrand")
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> wsMain.Name Then
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PageFields
If pf.Name = pfMain.Name Then
If pfMain.CurrentPage = "(All)" Then
pf.CurrentPage = "(All)"
Exit For
End If
For Each pi In pf.PivotItems
If pi.Name = pfMain.CurrentPage Then
pf.CurrentPage = pi.Name
Exit For
End If
Next pi
End If
Next pf
Next pt
End If
Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

So I tired this code:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim ptTable As PivotTable, ptItem As PivotItem, vFields As Variant, boolMulti As Boolean, lngField As Long
Dim ws As Worksheet
On Error GoTo ExitPoint
vFields = Array("brand", "month", "product name (product)")
Application.EnableEvents = False
For Each ws In ThisWorkbook.Worksheets
For Each ptTable In ws.PivotTables
If ptTable <> Target Or ws.Name <> Sh.Name Then
ptTable.ManualUpdate = True
For lngField = LBound(vFields) To UBound(vFields) Step 1
boolMulti = Target.PivotFields(vFields(lngField)).EnableMultiplePageItems
With ptTable.PivotFields(vFields(lngField))
.ClearAllFilters
Select Case boolMulti
Case False
.CurrentPage = Target.PivotFields(vFields(lngField)).CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each ptItem In Target.PivotFields(vFields(lngField)).PivotItems
.PivotItems(ptItem.Name).Visible = ptItem.Visible
Next ptItem
.EnableMultiplePageItems = boolMulti
End Select
End With
Next lngField
ptTable.ManualUpdate = False
End If
Next ptTable
Next ws
ExitPoint:
Application.EnableEvents = True
End Sub


and when I make changes on the main PT it changes the other PT to other data that does not match the main PT. I am trying to use 3 report filters. "Branch, Product name (product), and Month).

Please help! I really need to be able to use the multi select for criteria.

Hi chudok,

Edit this post using the green Edit button, Then Go Advanced, highlight the code and click the # icon just above the Message area. This will put code markers around your code so I can read it. Save your post and I'll look.

Without reading your code too hard, do you need to do a refresh all after changeing the filters?