Search code examples
excelvba

PivotTable Refresh error in the same sheet

I am using the below simple code to refresh the Pivottable automatically when any data changes in the source table which is working normally if the pivottable and datasource are on the different sheets.

Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable

Set wb = ActiveWorkbook

For Each ws In wb.Sheets
  For Each pt In ws.PivotTables
    pt.PivotCache.Refresh
  Next pt
Next ws

I could have used simply but i want to refresh only pivottables.

ActiveWorkbook.RefreshAll

I am using it on the WorksheetChange

Private Sub Worksheet_Change(ByVal Target As Range)

but I am receiving and error if the pivottable and the source table are on the same sheet.

Error

Error Code : -2147417848 (80010108) Method 'createpivottable' of object 'pivotcache' failed

Is there a way to keep the source and pivottable in the same sheet ?

Thank you,

Solution

  • You should disable events while refreshing since you will be changing the sheet from its Change event - use application.enableevents = false at the start and set it back to True at the end. In other words:

    Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable

Set wb = ActiveWorkbook
on error resume next
application.enableevents = false
For Each ws In wb.Sheets
  For Each pt In ws.PivotTables
    pt.PivotCache.Refresh
  Next pt
Next ws
on error goto 0
Application.enableevents = true