Microsoft Office Tutorials and References
In Depth Information
Synchronizing Page Fields of Pivot Tables
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub SynchPivotTables(BasePivot As PivotTable)¶
'Variable declaration¶
Dim Pivot As PivotTable¶
Dim Sheet As Worksheet¶
Dim BasePage As PivotField¶
Dim Page As PivotField¶
Dim Itm As PivotItem¶
'Synchorize the Pivot Tables in the Active Sheet¶
'Set Sheet = ActiveSheet¶
'or, loop through all the Pivot tables in the active workbook¶
For Each Sheet In ActiveWorkbook.Worksheets¶
'Loop through the Pivots in the worksheet¶
For Each Pivot In Sheet.PivotTables¶
'Try to synch if is not the base pivot table¶
If Not Pivot Is BasePivot Then¶
'Loop through the page fields of the Base Pivot¶
For Each BasePage In BasePivot.PageFields¶
'Clear the old page value¶
Set Page = Nothing¶
'Check if its in the pages of the Pivot¶
'Continue if error occurs¶
On Error Resume Next¶
Set Page = Pivot.PageFields(BasePage.Name)¶
On Error GoTo 0¶
'Is it there?¶
If Not Page Is Nothing Then¶
'Try to assign the current page¶
On Error Resume Next¶
Page.CurrentPage.Caption = _¶
BasePage.CurrentPage.Caption¶
'Succeed?¶
If Page.CurrentPage <> BasePage.CurrentPage Then¶
'Try, using the PivotItems collection¶
For Each Itm In Page.PivotItems¶
If Itm.Caption = BasePage.CurrentPage Then¶
Page.CurrentPage = Itm.Caption¶
End If¶
Next Itm¶
End If¶
On Error GoTo 0¶
End If¶
Next BasePage¶
End If¶
Next Pivot¶
Next Sheet¶
End Sub¶
Exl
Search JabSto ::




Custom Search