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
'This works and doesn't produce the above error
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!