Updating Pivot Tables when Source changes

[jake_88]

Hi everyone

I have an excel spreadsheet that is linked to a SharePoint list. When I open the excel file, the source data goes to the SharePoint list and updates the data. I then use this data and built 8 pivot tables for various purposes. Due to the filtering and grouping requirements, I chose to separate the pivot cache. so there are 8 pivot tables and 8 pivot cache. When I manually go into each pivot table and select refresh, it is almost instant...so I assumed the performance is acceptable. Currently the source data only has 600 rows.

Now when I wrote simple script to update the pivot tables when the source changes, it takes an average of 5 minutes, sometimes more but never less.

Here is my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim pt As PivotTable
For Each wks In Worksheets
For Each pt In wks.PivotTables
pt.RefreshTable
Next pt
Next wks
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I've also tried the following, but same result

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAllPivots
End sub

As well as, example below only shows one pivot for illustration purposes. by the way, even with this one pivot, it still takes about two minutes to update...

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("owssvr (1)").PivotTables("RejectReasonByWeek").PivotCache.Refresh
End sub

I'm at a loss, help!!