Enable Pivot Table Auto Refresh after being disabled

  • Thread starter Thread starter Steve LaFever
  • Start date Start date
S

Steve LaFever

I believe I may have accidentally clicked on the box
to "Enable Automatic Query refresh for all workbooks and
do not show this box again". How do you manage to
recreate this option? I have some very complicated
workbooks with numerous worksheets that link to each other
and therefore, I have set up auto refresh queries to
simplify the process. However, it now auto refreshes and
as a result I am unable to open prior workbooks to reprint
previous data as it autorefreshes for the current data.
Any ideas?
 
Steve,

Try running this macro, changing the sheet and pivot table names:

Sub PTManualUpdate()
Worksheets("Sheet1").PivotTables("Pivot1").ManualUpdate = True
End Sub

HTH,
Bernie
 
Steve LaFever said:
I believe I may have accidentally clicked on the box
to "Enable Automatic Query refresh for all workbooks and
do not show this box again". How do you manage to
recreate this option? I have some very complicated
workbooks with numerous worksheets that link to each other
and therefore, I have set up auto refresh queries to
simplify the process. However, it now auto refreshes and
as a result I am unable to open prior workbooks to reprint
previous data as it autorefreshes for the current data.
Any ideas?
Click to expand...

I had the same problem, resolution:

Path: HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options
Key: QuerySecurity

Possible Values:
0 = Prompt on all workbooks
1 = Do Not Prompt on any workbooks, and do NOT allow automatic query
refresh
for any workbooks
2 = Do Not Prompt on any workbooks and ALLOW automatic query refresh
on all
workbooks (this, of course, is NOT recommended)

The value on my PC after ..."Enable Automatic Query refresh for all
workbooks and
do not show this box again"... was 2. I've changed it to 0 manually
and this worked fine.

By, Eta
 
Back
Top