Auto refresh pivottables data in excel on first run

Question

Background : I have 2 excel sheets:

Contains pivot tables and a Macro "Refresh"
Data from SQL server

Macro contains VBA code for refreshing data and updating pivot tables automatically. The data is updated every night. The following is the VBA code:

 Sub Refresh()
 ActiveWorkBook.RefreshAll 
 End Sub

Problem:

When I run my Macro for first time, it takes 5min to execute and my pivot table is not updated with new data.
When I run it for second time, it executes perfectly.

I want my Macro to execute perfectly on first run.

Any help is greatly appreciated.

Note: This is my first post so please tolerate my edit.

Possibly this is because the first time, the dataset is refreshed from the datasource. After that, it is cached in the pivotcaches, so unless the source data changes, it executes much faster the subsequent times! — Our Man in Bananas
** @Philip :** You are right. On the first click it refreshes the data set from sql server then on second click it refreshes the pivot cache. — user42995

user42995 user42995 · Accepted Answer · 2013-04-25T20:31:28

I had solved my problem:

I removed Macro and used an Active-x-Control button.So, on button_click() to refreshes ExternalData as well as pivotTables
So, here is the code: I removed the code below

ActiveWorkBook.RefreshAll

and updated it with

Private Sub CommandButton1_Click()
Dim Data_query As Worksheet
Set Data_query = Sheets("Data")
Data_query.Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Application.ScreenUpdating = False
Sheets("sheet1").Select
Sheets("sheet1").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("sheet1").PivotTables("PivotTable1").RefreshTable
End Sub

So now both ExternalData as well as pivotTables get refreshed at same time.

Auto refresh pivottables data in excel on first run

2 Answers