Change report firlds in pivot tables and charts with one selection
Hi -
I have several pivot tables and pivot charts on one worksheet. I want all reports fields in the tables and charts to be the same and therefore want the worksheet to automate this when just one table or chart fields are changed (rather than having to edit each one). I have used the below code that is working perfectly but only for the pivot tables - the pivot charts do not alter. This is the first attempt to write code that alters pivot tables and charts so am pretty new to how to add to this code to achieve this. Any help hugely appreciated Thanks in advance Paul Option Explicit Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) On Error Resume Next Dim wsMain As Worksheet Dim ws As Worksheet Dim ptMain As PivotTable Dim pt As PivotTable Dim pfMain As PivotField Dim pf As PivotField Dim pi As PivotItem Dim bMI As Boolean On Error Resume Next Set wsMain = ActiveSheet Set ptMain = Target Application.EnableEvents = False Application.ScreenUpdating = False 'change all fields for all pivot tables on active sheet For Each pfMain In ptMain.PageFields bMI = pfMain.EnableMultiplePageItems For Each pt In wsMain.PivotTables If pt < ptMain Then pt.ManualUpdate = True Set pf = pt.PivotFields(pfMain.Name) bMI = pfMain.EnableMultiplePageItems With pf .ClearAllFilters Select Case bMI Case False .CurrentPage = pfMain.CurrentPage.Value Case True .CurrentPage = "(All)" For Each pi In pfMain.PivotItems .PivotItems(pi.Name).Visible = pi..Visible Next pi .EnableMultiplePageItems = bMI End Select End With bMI = False Set pf = Nothing pt.ManualUpdate = False End If Next pt Next pfMain Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com