Pivot table in excel 2007 not updating automatically

blossompark
blossompark used Ask the Experts™
on
Hi,
       I have a pivot table in an excel 2007 file set to “Refresh data when opening the file”
However when I open the file I have to Refresh the Pivot table manually.
Any ideas  how I can get this option to work ?
thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try adding this vba code in your pivot table sheet

Private Sub Worksheet_Activate()
    Call UpdateIt
End Sub
Commented:
Can you please try putting this code on your sheet to see if the Pivot Table refreshes when the file is opened?

jppinto
Private Sub Workbook_Open()
    Me.Worksheets("SheetWithPivotTable").PivotTables(1).RefreshTable 'change for your sheet name
End Sub

Open in new window

I would imagine the reason the option you selected isn't working is because the source data has changed. This seems to be by design.

here is a link that might assist in getting a macro to do the refresh for you.

http://mousetraining.blogspot.com/2009/07/how-to-make-pivot-tables-refresh_23.html

Let me know if this helps

.Dave

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Most Valuable Expert 2011
Top Expert 2011
Commented:
What is the source for the pivot table? Is it linked directly to a range, or to external data, or to a range that gets its data from an external source?

Author

Commented:
Hi sanjithml, jppinto, David and Rorya...
Oh...and i thought all i had to do was tick the box
 pivot1
updating manually
 pivot2
produces the results
 pivot3
roryo...it links directly to a range on another tab

Author

Commented:
jppinto
will try your suggestion...once i figure out how to add the code!!
Commented:
Alt+F11 takes you to the VBA editor. Then just add the code to the code window on the right, selecting the "ThisWorkbook" on the left.

jppinto
Capturar.JPG
Most Valuable Expert 2011
Top Expert 2011
Commented:
Is the range on the other tab linked to another source, or just data on a sheet?
when you talk abt adding code it is VBA and nothing else.
you have to go to VBA mode and type in the code.

Already jppinto has given the option to go to VBA....
hope this works....

Author

Commented:
hi rorya...just data on a sheet
Top Expert 2010
Commented:

Author

Commented:
just one other piece of information... they're are other tabs in the file that have pivot tables that are updating fine...it is just this one that needs to be manually updated

Author

Commented:
Hi sanjithml, ippinto, David, rorya , matthewspatrick
I have populated the column that the pivot  references  with dummy data and now it is updating when opening...
the excel file i am using is a template that gets xml data dumped into it ...
it now seems to be working ok..
thanks for all your inputs, greatly informative and appreciated :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial