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