PIVOTTABLE not updating on File open

[Pete_Bristol]

Dear Forum,

Please can you help?

I have a workbook containing about 10 pivottables which are all DEFINATELY set to "Refresh on File open".

8/10 of them update as expected but two dont. I thought maybe these two connected/shared in some way the same pivotcache but they are both different. However, both are linked to pivot charts.

Please can anybody offer advice as to why these two tables might not update? Configurations? Settings? etc

Thanks in anticipation,

Regards,

Pete

 

[MarkAndrews]

try this in a workbook open event

Public Sub Refresh()

Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True

End Sub

 

[Pete_Bristol]

Hi Mark,

Thanks for prompt reply.

Tried as you suggested, I watched as all pivot tables got updated EXCEPT the normal two!

Baffled!

Pete

 

[MarkAndrews]

Would it be a big job to redo these Pivs?

(The problem 2 that is)

If you've checked their settings, i'm baffled?

 

[Pete_Bristol]

Mark,

Literally just done that - same answer!

One point that maybe I should point out just in case it makes a difference is that the datatable which drives these two offending pivottables is a block of data generated by complex calculations using GETPIVOTDATA to another pivottable.

However, I cant see that this should make a difference. Its just "data"? Perhaps it does?

Pete

 

[MarkAndrews]

Mark,

Literally just done that - same answer!

One point that maybe I should point out just in case it makes a difference is that the datatable which drives these two offending pivottables is a block of data generated by complex calculations using GETPIVOTDATA to another pivottable.

However, I cant see that this should make a difference. Its just "data"? Perhaps it does?

Pete

Could be a problem with your formula - from wherever this data derives from, if the other pivot table has changed (extra rows) and the formula isn't anchored ($ or F4) the reference will have changed, which wont reflect on these 2 offending tables?

Worth a shot?

 

[Pete_Bristol]

Mark,

Unfortunately not.

The data in the grid (generated by GETPIVOTDATA to another pivot) is definately correct.

I completely redone these reports in a new, blank workbook and get the same problem!

The source PIVOT updates on file open, the GETPIVOTDATA gets updated, the the Pivot linked to this does not!

There just has to be a reason but it escapes me!

Pete

 

[MarkAndrews]

I honestly dont have a clue then?

Maybe someone else can shed some light on this?

 

[Pete_Bristol]

Mark,

When I left work last night it was bugging me so much that i recreated a much simplified example at home (in the middle of the night!) and got exactly the same result. I can reproduce it every time!

For whatever reason, pivottables that have their source as another range populated with getpivotdata to another pivot DO NOT update.

A chap from another forum suggested a workaround, which does work, in that you have to tick the box about saving data with file. As this clears out the data, I guess Excel has no choice but to reload it?

Anyhow, many thanks for your time and help.

Regards,

Pete

 

[MarkAndrews]

Could you not replace GETPIVOTDATA with a vlookup?

Not ideal, but a solution non the less...?