Forum Discussion

aaaaaa805's avatar
aaaaaa805
Copper Contributor
Nov 26, 2021

Refreshing Pivot table failing error 1004

Hello,

After switching to Office 365 Excel version: 2109 1443.20342, from 2016 I am having some issues running an Excel file which refreshes a PivotTable on opening the workbook.

 

The Excel file defaults to opening to a sheet different than where the PivotTable is and this causes some issues when using the RefreshTable function.

 

I haven't been able to figure out why it has started to fail and throw this error in this later version compared to the old one, but below is where I tracked down the error to and I guess a solution to use PivotCache refresh instead, but I would prefer to understand the issue.

'This code fails with error 1004: select method of range class failed
ThisWorkbook.Sheets("Sheet1").Activate
ActiveSheet.PivotTables("PivotTable1").RefreshTable

'This works and doesn't produce the above error
ThisWorkbook.Sheets("Sheet1").Activate
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

 

Any ideas what could be the cause of this?

excel
Macros and VBA

1 Reply

  • ymnalya1615's avatar
    ymnalya1615
    Copper Contributor
    Nov 14, 2022
    I encountered the same error and cannot fathom any reason behind them either but, it worked after I closed and reload the excel in my case.
    I assumed that there is something wrong in the excel or my PC got too heated after playing with power query for a long time.

    So, the solution in my case is : restart the excel.

    I hope it can help anyone with the same problem! Hope you have a great and easy day!

Resources