Hi All,
Recently I was looking for a macro to control all of the pivot tables in my workbook to update all of the monthly dates in one go rather than one at a time. I currently use this macro:
Which works exactly as I need it too! But just to throw a curve ball in there my manager has asked that I add in some pivot tables that show the rolling 12 months, which essentially means there are 3 pivot tables, on each tab, that now need to be excluded from the macro. Is there a way to do this? Perhaps excluding all those pivots with the same name? or excluding pivots with names X, Y and Z? Or perhaps since each tab is set up in the same format could it be that any pivot table beyond column 'U' is exempt from the macro?
I'm not sure how to deal with this issue in any way but those were just a few ideas I had?
Can someone please assist! Is this actually possible?
Many thanks
Harvey :
Recently I was looking for a macro to control all of the pivot tables in my workbook to update all of the monthly dates in one go rather than one at a time. I currently use this macro:
Code:
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim wsPTF As Worksheet
Dim ptMain As PivotTable
Dim ptF As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pfPTF As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
Dim bPTF As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
If wsMain.Name <> Me.Name Then GoTo exitHandler
Set wsPTF = Sheets("Change Month")
Set ptMain = Target
Set ptF = wsPTF.PivotTables("PT_List")
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
bPTF = False
For Each pfPTF In ptF.PageFields
If pfMain.Name = pfPTF.Name Then
bPTF = True
Exit For
End If
Next pfPTF
If bPTF = False Then
Exit For
End If
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & "_" & pt <> wsMain.Name & "_" & 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 ws
Next pfMain
exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Could not update all pivot tables"
Resume exitHandler
End Sub
I'm not sure how to deal with this issue in any way but those were just a few ideas I had?
Can someone please assist! Is this actually possible?
Many thanks
Harvey :
Last edited by a moderator: