Refreshing Pivot table failing error 1004

%3CLINGO-SUB%20id%3D%22lingo-sub-3008866%22%20slang%3D%22en-US%22%3ERefreshing%20Pivot%20table%20failing%20error%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3008866%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EAfter%20switching%20to%20Office%20365%20Excel%20version%3A%202109%201443.20342%2C%20from%202016%20I%20am%20having%20some%20issues%20running%20an%20Excel%20file%20which%20refreshes%20a%20PivotTable%20on%20opening%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Excel%20file%20defaults%20to%20opening%20to%20a%20sheet%20different%20than%20where%20the%20PivotTable%20is%20and%20this%20causes%20some%20issues%20when%20using%20the%20RefreshTable%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20been%20able%20to%20figure%20out%20why%20it%20has%20started%20to%20fail%20and%20throw%20this%20error%20in%20this%20later%20version%20compared%20to%20the%20old%20one%2C%20but%20below%20is%20where%20I%20tracked%20down%20the%20error%20to%20and%20I%20guess%20a%20solution%20to%20use%20PivotCache%20refresh%20instead%2C%20but%20I%20would%20prefer%20to%20understand%20the%20issue.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-vbnet%22%3E%3CCODE%3E'This%20code%20fails%20with%20error%201004%3A%20select%20method%20of%20range%20class%20failed%0AThisWorkbook.Sheets(%22Sheet1%22).Activate%0AActiveSheet.PivotTables(%22PivotTable1%22).RefreshTable%0A%0A'This%20works%20and%20doesn't%20produce%20the%20above%20error%0AThisWorkbook.Sheets(%22Sheet1%22).Activate%0AActiveSheet.PivotTables(%22PivotTable1%22).PivotCache.Refresh%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221004.png%22%20style%3D%22width%3A%20359px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329706i68DD695B83C2F71C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%221004.png%22%20alt%3D%221004.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20what%20could%20be%20the%20cause%20of%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3008866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

1004.png

 

Any ideas what could be the cause of this?

0 Replies