VBA code; Pivottable refreshes one step behind

Hard to explain this one but I try. On sheet1 I get external acount data from external datasource. On sheet2 I have a pivottable getting data from sheet1. This work fine when I update the sheets manually. But I want it to run by using vba code. My code looks like below:



The problem is that the pivot refreshes with the previous dataset from sheet1. Almost like if I had run the pivot update before updateing data on sheet1. If I run the code a second time without changing parameter then the pivot gets the correct data.

Think this has to be a common problem so I hope someone understand my bad english and can help.
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
Check the properties of your data connection and make sure it is not set to refresh in the background and then your code will be fine.

may be..

'just to ensure the external data get updated before refeshing the pivot table
'pause for 3 seconds
Application.Wait Now + TimeSerial(0, 0, 3)

Open in new window

hallpettAuthor Commented:
The pause trick does'nt make any difference. I still have to click my "Get data" button twice for the pivot to refresh with the latest data. If I insert a breakpoint on line 5 in your code, and hit F5 when the code stops, then it works. Beats me!
hallpettAuthor Commented:
Thank you. Would not have found that solution on my own.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.