Auto refresh pivottables data in excel on first run

Question

Background : I have 2 excel sheets:

1. Contains pivot tables and a Macro "Refresh"
2. 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:

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

2. 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.

Answer 1

I had solved my problem:

1. I removed Macro and used an Active-x-Control button.So, on button_click() to refreshes ExternalData as well as pivotTables

2. 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.