excel - Pivot Table does not refresh using VBA

Question

I have a pivot created in excel 365

My sourcedata is in a Ms Access Query

I built a macro in Excel to refresh the pivot but I got an error message.

Runtime error 1004

Unable to get PivotTables property of the worksheet class

The code I wrote:

Sub Refresh_Pivot01()
'
' Refresh_Pivot01 Macro
'
Sheets("PivotData").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh*

End Sub

The code ThisWorkbook.RefreshAll does only work if the sourcedata is in the same excelsheet and in my case the sourcedata is in a MS Access Query

There must be something wrong but I can not figure it out ! Can someone help me out here ??

Answer 1

No need to select stuff (read here why)

Fully qualify your objects.

See how I set a reference to the workbook, sheet and pivot table by using their pointers

Replace the TheWorkbookName with the actual workbook name

Public Sub RefreshPivotTable()
    
    Dim targetWorkbook As Workbook
    Set targetWorkbook = Workbooks("TheWorkbookName")
    
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets("PivotData")
    
    targetSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Let me know if it works