Automatically Changing Page Fields in Same Tab (Excel 2007)

[sbecker61]

I have an Excel 2007 document that has 6 tabs, each of those tabs has one to three different pivot tables in them. Common among all those pivot tables in the page field is a field called "Extract Date".

I was given the following code to change Extract Date in all of the other tabs when I choose the Page Field in the tab called "AE Pivot".


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("AE Pivot")
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


This code works great and updates all the page fields in the different tabs. The only challenge I have is updating the page fields in the same tab (AE Pivot). How do I modify this code so it changes all the page fields in the same tab (AE Pivot) as well as the other tabs?

I'm sure this is an easy mod for the Excel masters here.

thank you in advance.

Steve