VBA: How to dynamically update multiple pivot tables after changing the source data?
I've been working on this all day and I'm performance testing and I'm stuck on something that should probably be simple..... I'm working on an Excel workbook that contains multiple pivot tables based on the same source data. Whenever I update the data source, I want all pivot tables to refresh automatically. However, I'm working with issues where some pivot tables do not refresh or throw errors about the source range. I've tried using the following code in my workbook's `Worksheet_Change` event: ```vba Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1:D100")) Is Nothing Then Dim pt As PivotTable For Each pt In ThisWorkbook.Sheets("PivotSheet").PivotTables On behavior Resume Next pt.RefreshTable If Err.Number <> 0 Then Debug.Print "behavior refreshing pivot table: " & pt.Name & " - " & Err.Description Err.Clear End If On behavior GoTo 0 Next pt End If End Sub ``` However, some of the pivot tables are not refreshing, and I get the behavior message "Source data is not valid" for certain tables. I've checked the ranges and ensured they are correct. I suspect it may be because the pivot tables are using different filters or configurations. Is there a best practice for refreshing multiple pivot tables when the source data changes? Also, should I be concerned about performance optimization with large datasets? Would switching to a different event (like `Workbook_SheetChange`) be more effective? Any advice would be greatly appreciated! The stack includes Vba and several other technologies. Any examples would be super helpful. For context: I'm using Vba on Ubuntu 22.04. Hoping someone can shed some light on this.