refresh both the External data source and pivot tables together within a time schedule

Under the connection properties, uncheck "Enable background refresh". This will make the connection refresh when told to, not in the background as other processes happen.

With background refresh disabled, your VBA procedure will wait for your external data to refresh before moving to the next line of code.

Then you just modify the following code:

ActiveWorkbook.Connections("CONNECTION_NAME").RefreshSheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh

You can also turn off background refresh in VBA:

ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False

I used the above answer but made use of the RefreshAll method. I also changed it to allow for multiple connections without having to specify the names. I then linked this to a button on my spreadsheet.

Sub Refresh()    Dim conn As Variant    For Each conn In ActiveWorkbook.Connections        conn.ODBCConnection.BackgroundQuery = False    Next conn    ActiveWorkbook.RefreshAllEnd Sub

I think there is a simpler way to make excel wait till the refresh is done, without having to set the Background Query property to False. Why mess with people's preferences right?

Excel 2010 (and later) has this method called CalculateUntilAsyncQueriesDone and all you have to do it call it after you have called the RefreshAll method. Excel will wait till the calculation is complete.

ThisWorkbook.RefreshAllApplication.CalculateUntilAsyncQueriesDone

I usually put these things together to do a master full calculate without interruption, before sending my models to others. Something like this:

ThisWorkbook.RefreshAllApplication.CalculateUntilAsyncQueriesDoneApplication.CalculateFullRebuildApplication.CalculateUntilAsyncQueriesDone