1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Refresh and recalculating in a specific order (VBA excel)

Discussion in 'Programming/Internet' started by xearow, Sep 12, 2018.

  1. xearow

    xearow Guest

    I need to be able to refresh and calculate everything in a specific order. The order would go like this: Refresh data connections (there are 4 of them) Stop refreshing data connections and their sheets all together Refresh all of the pivot tables within all of the sheets Wait for each one to finish before moving on to the next Then recalculate each Sheet (except for the data connection) and wait for each one to finish before moving on to the next I have some pieces of how to do this, but i'm not sure how to make it all work as intended.

    Sub refresh()

    Application.Calculation = xlCalculateManual

    ActiveWorkbook.Connections("DATA").refresh
    'wait for the refresh to finish?
    ActiveWorkbook.Connections("DATA_LEADS").refresh
    'wait for refresh to finish

    ActiveWorkbook.Connections("SURVEY_DATA").refresh
    'wait
    ActiveWorkbook.Connections("SERVICE_TECHS").refresh
    'wait


    Dim pivots As PivotTable
    Dim allsheets As Worksheet

    For Each allsheets In ThisWorkbook.Worksheets

    For Each pivots In allsheets.PivotTables
    pivots.RefreshTable
    Next pivots

    Next allsheets
    Sheet4.Calculate
    'wait
    Sheet5.Calculate
    'wait
    Sheet6.Calculate
    'wait
    Sheet7.Calculate
    'wait
    Sheet8.Calculate
    'wait
    Sheet9.Calculate




    End Sub


    I might be going at this completely the wrong way too. Any advice/solutions would be great!

    Login To add answer/comment
     

Share This Page