Update Page Field in Multiple Pivot Tables

rastus

Board Regular
Joined
Jul 8, 2005
Messages
157
Hi

I have Pivot Table1 in Worksheet1, Pivot Table2 in Worksheet2 and Pivot Table3,4,5 & 6 in Worksheet3. I want to be able to update the page field ("Date") in Pivot Table1 so the date in all the other Pivot Tables updates to the same value.

This topic has been raised in a previous thread and I was pointed to a VBA code provided here: http://www.contextures.com/excelfiles.html:


Option Explicit

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("Sales Pivot")
Set ptMain = Target

Application.EnableEvents = False

For Each pfMain In ptMain.PageFields
Debug.Print pfMain.Name
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
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

End Sub


I copied this code into my excel file and saved. When I reopened the file and changed the date in Pivot Table 1 it worked fine, but afterwards the file froze i.e. I was unable to click in any cell anywhere in the file so I had to close the file. All my other macros now have a 1004 error.

I'm not sure why this is happening. Can anyone help?

Thanks

R
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top