Macro to refresh pivot tables produces run-time error 440

[Legacy 327851]

Hi there,

I'm using Excel 2013 and have a workbook with 5 sheets of data, all from different external data sources.

I've then created a new sheet which is basically my report. It has a drop-down for a user to select their ID, and this then updates some vlookups, but the reason for the macro is that I have 3 pivot tables in the report. To make it easier for users who would be easily confused by having to refresh the tables I've created a macro that refreshes them all when you hit the big button that says 'run report'.


This is my first experience with macros so I have simply hit Record and then refreshed each of my pivot tables in turn, and then stopped recording. I can copy out the code if needed.

Now - the problem. The macro works fine for me, and for about 90% of the users. I have a couple of people who get an error though, and I can't figure out if there's something wrong with the macro, or elsewhere. I suspect elsewhere as surely it would affect everyone otherwise?

The error is Run-time error '440': Method 'Refresh' of object 'PivotCache' failed.

Can anyone help?

 

[Kamolga]

Could you copy the code? I suspect you have soething like Version:=xlPivotTableVersion14 in your code and Are those users using another version of excel...

Also do they accept the security question about running macro?

 

[Kamolga]

Could you poste the code?

I suspect you have something like

[COLOR=#333333][FONT=Segoe UI]Version:=xlPivotTableVersion14 [/FONT][/COLOR]

and some users to use a different version. If so, try to erase this part and see if it still works.

Sorry for 2X messaging

 

[Legacy 327851]

Could you copy the code? I suspect you have soething like Version:=xlPivotTableVersion14 in your code and Are those users using another version of excel...

Also do they accept the security question about running macro?

Thanks. Here's the code:

Sheets("Report").Unprotect Password:="[REDACTED]"
    Sheets("TaskData").Range("I2,J10000").NumberFormat = "DD/MM/YYYY"
    Range("F2").Value = "Report date: " & Date
    Range("C28").Select
    Sheets("Report").PivotTables("PivotTable2").PivotCache.Refresh
    Range("I6").Select
    Sheets("Report").PivotTables("PivotTable5").PivotCache.Refresh
    Range("I28").Select
    Sheets("Report").PivotTables("PivotTable4").PivotCache.Refresh
    Sheets("Report").Protect Password:="[REDACTED]"

I'll ask them to double check they are accepting macros. We are all using Excel 2013.

 

[Kamolga]

Hi,

Your code seems perfectly valid. Maybe some ID do not have all pivots? I would normally use the code below (I believe it works even on code protected sheets)

ActiveWorkbook.RefreshAll