Update Multiple PivotTables in Excel using VBA-CodePudding

I have 3 sheets where the first sheet contain the data, second is the pivot table while the third sheet is the pivot chart generate from the pivot table. I want to create a VBA update button whereby it will update both the pivot table and pivot chart once there is changes in the data source.

Here's what I have done so far:

Sub Update_Pivot()

    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim lr As Long
    Dim rng As Range
    
    lr = ActiveWorkbook.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    Set rng = ActiveWorkbook.Sheets("Data").Range("A3:AR" & lr)
    
    For Each ws In ActiveWorkbook.Worksheets
    
    For Each pt In ws.PivotTables
             pt.ChangePivotCache ActiveWorkbook. _
                PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng)
    Next pt
    Next ws
      
End Sub

However, I receive an error whereby the error was in the pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng) saying that Run-time error '5'.

Does my coding correct? How can I solve this error?

CodePudding user response：

If you are looping through sheets and on each sheet, looping through pivots, you should be pretty close...

Add the code below...after

For Each pt

See if that helps...

Application.ScreenUpdating = False 'This line disable the on screen update for better performance, the blink you see, you could delete both lanes but it will run slower
Dim myChart As ChartObject
For Each myChart In ActiveSheet.ChartObjects
    myChart.Chart.Refresh
Next myChart
Application.ScreenUpdating = True'This line reenable the on screen update for better performance, the blink you see, you could delete both lanes but it will run slower

CodePudding user response：

This should work:

Sub Update()

For Each Sheet In ThisWorkbook.Worksheets
            For Each Pivot In Sheet.PivotTables
                Pivot.RefreshTable
                Pivot.Update
            Next
            Dim myChart As ChartObject
            For Each myChart In ActiveSheet.ChartObjects
                myChart.Chart.Refresh
            Next myChart
Next
For Each Chart In ThisWorkbook.Charts ' if chart is not part of a sheet, but on its own 
            Chart.Refresh
        Next
End Sub