PIVOTTABLE not updating on File open

Pete_Bristol

Board Regular
Joined
Mar 8, 2006
Messages
122
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
try this in a workbook open event

Public Sub Refresh()

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

End Sub
 

Pete_Bristol

Board Regular
Joined
Mar 8, 2006
Messages
122
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

Board Regular
Joined
Mar 8, 2006
Messages
122

ADVERTISEMENT

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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

Board Regular
Joined
Mar 8, 2006
Messages
122

ADVERTISEMENT

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

Well-known Member
Joined
May 2, 2006
Messages
1,963
I honestly dont have a clue then?

Maybe someone else can shed some light on this?
 

Pete_Bristol

Board Regular
Joined
Mar 8, 2006
Messages
122
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
Could you not replace GETPIVOTDATA with a vlookup?

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

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,959
Members
416,952
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top