Refreshing Pivot table failing error 1004

[aaaaaa805]

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?

[#2]

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!