VBA code; Pivottable refreshes one step behind

Posted on 2012-09-21
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:



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.
Question by:hallpett
    LVL 18

    Expert Comment


    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


    Author Comment

    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!
    LVL 85

    Accepted Solution

    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.

    Author Closing Comment

    Thank you. Would not have found that solution on my own.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now