?
Solved

VBA code; Pivottable refreshes one step behind

Posted on 2012-09-21
4
Medium Priority
?
504 Views
Last Modified: 2012-09-21
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.
0
Comment
Question by:hallpett
  • 2
4 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38421103
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
 

Author Comment

by:hallpett
ID: 38421178
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 38421869
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
 

Author Closing Comment

by:hallpett
ID: 38422246
Thank you. Would not have found that solution on my own.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question