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:

  Sheets("sheet1").Select
  ActiveWorkbook.RefreshAll

  Sheets("sheet2").Select
  Range("B3").Select
  ActiveSheet.PivotTables("Saldo").PivotCache.Refresh

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.
hallpettAsked:
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.
0
 
krishnakrkcCommented:
Hi

may be..

ActiveWorkbook.RefreshAll
'just to ensure the external data get updated before refeshing the pivot table
'pause for 3 seconds
Application.Wait Now + TimeSerial(0, 0, 3)
Worksheets("sheet2").PivotTables("Saldo").PivotCache.Refresh

Open in new window


Kris
0
 
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!
0
 
hallpettAuthor Commented:
Thank you. Would not have found that solution on my own.
0
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.