VBA Pivot Tables- run-time error 1004 Unable to get the PivotTables properties of the worksheet class.

[Maggy0911]

I have one data source sheet and 3 pivot tables that I want to all update automatically. I am getting this run-time error 1004 when I try to input a new row or make any changes. Below is the code I have for all 3 pivot tables. Can someone help me please?

Run-time error 1004 Unable to get the PivotTables properties of the worksheet class.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Data").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Data").PivotTables("PivotTable2").PivotCache.Refresh
Worksheets("Data").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

 

[V_Malkoti]

The error message means that one or more of these pivot tables are not present in Data sheet.

 

[Maggy0911]

I decided to start over and make it as simple as possible. I created a whole new workbook and created 1 pivot table off of the data source. I still got the error. I tried putting the code in this workbook instead of the data sheet and now I can make changes to the data source without getting a error. But when I looked at the pivot table its not updating automatically. Below is the code I am using. My data sheet is Sheet1 and my pivot table is Sheet2. I am so confused.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").PivotTables("Sheet2").PivotCache.Refresh
End Sub

I have one data source sheet and 3 pivot tables that I want to all update automatically. I am getting this run-time error 1004 when I try to input a new row or make any changes. Below is the code I have for all 3 pivot tables. Can someone help me please?

Run-time error 1004 Unable to get the PivotTables properties of the worksheet class.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Data").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Data").PivotTables("PivotTable2").PivotCache.Refresh
Worksheets("Data").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

 

[V_Malkoti]

Worksheet_Change is the name for sheet's event procedure - if you wanted the code to run every time you update Sheet2, then place Worksheet_Change code in VBA module of Sheet2.

At workbook level, the name of procedure is Workbook_SheetChange - it runs if any of the sheet is changed.

Also disable events before refreshing pivot cache.

Worksheet module code (whichever sheet's update should trigger refresh):

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("Sheet1").PivotTables("Sheet2").PivotCache.Refresh
    Application.EnableEvents = True

End Sub

Workbook module code (if updating any of the sheets should trigger a refresh):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("Sheet1").PivotTables("Sheet2").PivotCache.Refresh
    Application.EnableEvents = True
End Sub

 

[Maggy0911]

Thank you for this by the way. What would be the code if I had multiple pivot tables to automatically update from one source data sheet?

Worksheet_Change is the name for sheet's event procedure - if you wanted the code to run every time you update Sheet2, then place Worksheet_Change code in VBA module of Sheet2.

At workbook level, the name of procedure is Workbook_SheetChange - it runs if any of the sheet is changed.

Also disable events before refreshing pivot cache.

Worksheet module code (whichever sheet's update should trigger refresh):

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("Sheet1").PivotTables("Sheet2").PivotCache.Refresh
    Application.EnableEvents = True

End Sub

Workbook module code (if updating any of the sheets should trigger a refresh):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("Sheet1").PivotTables("Sheet2").PivotCache.Refresh
    Application.EnableEvents = True
End Sub

 

[V_Malkoti]

If both pivot table refresh depend on same sheet: If you have two pivot tables (Pivot1 in Sheet1 and Pivot2 in Sheet2) in that need to be refreshed whenever a change in Sheet5 occurs, your Sheet5 module code will be:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("Sheet1").PivotTables("Pivot1").PivotCache.Refresh
    Worksheets("Sheet2").PivotTables("Pivot2").PivotCache.Refresh
    Application.EnableEvents = True
End Sub

If each pivot table depends on a different sheet: Create Worksheet_Change for each sheet in which data is being changed to trigger Refresh. Or use the Workbook module event code Workbook_SheetChange().