PivotTable Not Refreshing With Data

user_3184087 0
Viewed 216

I have an Excel document with 4 Worksheets.

On Sheet1 I set my start & end dates for my data.
On Sheet2 is the data itself (provided by an external SQL database).
On Sheet3 & Sheet4 are PivotTables that use the second sheets data as their source.

I have created a VBA macro button on Sheet1 to refresh all.

I'll change my date range on Sheet1 then click the button but it only refreshes the data on Sheet2 and not the PivotTables on Sheet3 & Sheet4. I have to click the button a second time to refresh the PivotTables with the new data.

Here is the VBA code:

Sub Button2_Click()
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    Application.ScreenUpdating = True
End Sub

Even manually using the Refresh All button has the same outcome.

excel-2010 vba excel
1 Answers

It seems when you do a refresh all it is trying to refresh everything in parallel, probably to save time. However, I think since you have pivot tables which are dependent on data which needs to refresh from the DB, you have to ensure that your external data is refreshed prior to attempting to refresh the pivot tables.

I was able to reproduce the issue and doing the below resolved it for me. Just have to replace "Query1" with the name of your query.

ActiveSheet.ListObjects("Query1").QueryTable.Refresh BackgroundQuery:=False
For Each pc In ActiveWorkbook.PivotCaches
    pc.Refresh
Next

Edit: To get the query name, you can go to the "Data" tab on your ribbon you should see a button that says "Queries & Connections" clicking on that will expand a pane on the right side of the excel screen. In that pane clicking on the Queries tab on the top will give you a list of your queries that are set up

Screenshot of my QueryTable name

user_113543780
Related