0

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 ??

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
Tio_Chiel
  • 1

1 Answers1

0

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

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
  • Hi Richard, thanks for your quick reply; I copied your line of coding but got the same errormessage; Public Sub RefreshPivotTable() Dim targetWorkbook As Workbook Set targetWorkbook = Workbooks("Cemis-Recharge_02.xlsm") Dim targetSheet As Worksheet Set targetSheet = targetWorkbook.Worksheets("PivotData") targetSheet.PivotTables("PivotTable1").PivotCache.Refresh End Sub – Tio_Chiel Jan 06 '21 at 16:56
  • If you press `F8`key to debug the code, what line rises the error? – Ricardo Diaz Jan 06 '21 at 17:04
  • Application defined or object defined error – Tio_Chiel Jan 06 '21 at 17:07
  • That's the error. But what line in the code is raising it? – Ricardo Diaz Jan 06 '21 at 17:08
  • If I change the coding to "Pivottable2" I get another errormessage: Method Pivottables or object Worksheet failed – Tio_Chiel Jan 06 '21 at 17:09
  • Sorry I forgot to mention the error occurred on the last line: targetSheet.PivotTables("PivotTable1").PivotCache.Refresh – Tio_Chiel Jan 06 '21 at 17:10
  • You have to check what's the PivotTable Name [read this](https://support.microsoft.com/en-us/office/rename-a-pivottable-or-pivotchart-f8b73921-3494-48e2-a271-ce6c5d371cf4) – Ricardo Diaz Jan 06 '21 at 17:14
  • Hi Ricardo, In pivot table options I can see the name is PivotTable1 so that must be ok... – Tio_Chiel Jan 06 '21 at 17:20
  • Got it. If you right click the pivot table and select refresh what happens? – Ricardo Diaz Jan 06 '21 at 17:25
  • Then I see 1 milli second on the left corner of my excelsheet that the backgroundquery is running and than the pivot refreshes. So manually it does refresh – Tio_Chiel Jan 06 '21 at 17:32
  • The strange thing is that in some excelsheets (all xlsm format) it works (other query in Ms Access but same database) but in some it does not !! – Tio_Chiel Jan 07 '21 at 13:20
  • So the problem is not the procedure. You’ll have to check the source data and the way the pivot table is linked – Ricardo Diaz Jan 07 '21 at 13:25