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