title: On workbook open, Excel Macro to refresh all data connections sheets and pivot tables and then export the pivot to csv
body: <p>I have an Excel File which has CSV Data sources and Pivot tables, I want to refresh all the data sources and pivot tables automatically and export one pivot table as CSV on opening the excel file. </p>
  
  <p>I tried the below code, but this code export the CSV file before the data getting refreshed.</p>
  
  <p>please help with a solution. Thanks in advance.</p>
  
  <pre><code>Private Sub Workbook_Open()
      ThisWorkbook.RefreshAll
      Run "Macro1"
  End Sub
  
  
  Sub Macro1()
  
   Dim ws As Worksheet, newWb As Workbook
   Dim SaveToDirectory As String
  
   SaveToDirectory = "C:\Macro\"
  
   Application.ScreenUpdating = False
   For Each ws In Sheets(Array("locationwise"))
       ws.Copy
       Set newWb = ActiveWorkbook
       With newWb
          .SaveAs SaveToDirectory &amp; ws.Name, xlCSV
          .Close (False)
       End With
   Next ws
  Application.ScreenUpdating = True
  Application.DisplayAlerts = False
  End Sub
  </code></pre>