none
Refreshing a VBA Macro Periodically RRS feed

  • Question

  • Hi All

    I have assigned the below code that refreshes my data to a button. I was wondering if there was a piece of code that allows the data to automatically refresh/run every 20minutes???

    Sub RESFRESHCUSTOMERORDERS()
    '
    ' RESFRESHCUSTOMERORDERS Macro

        ActiveSheet.Unprotect
        Sheets("KYPERA").Visible = True
        Sheets("KYPERA").Select
        Sheets("PIVOTS").Visible = True
        Sheets("KYPERA").Select
        Range("B7").Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
        Range("F7").Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
        Range("N7").Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
        Range("BT7").Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
        Range("BZ7").Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
       
        Sheets("PIVOTS").Select
        Range("B5").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.REFRESH
        Range("F5").Select
        ActiveSheet.PivotTables("PivotTable2").PivotCache.REFRESH
        Range("J5").Select
        ActiveSheet.PivotTables("PivotTable3").PivotCache.REFRESH
        Range("N5").Select
        ActiveSheet.PivotTables("PivotTable4").PivotCache.REFRESH
        Range("R5").Select
        ActiveSheet.PivotTables("PivotTable5").PivotCache.REFRESH
        Range("W5").Select
        ActiveSheet.PivotTables("PivotTable7").PivotCache.REFRESH
       
        Sheets("PIVOTS").Select
        ActiveWindow.SelectedSheets.Visible = False
        Sheets("KYPERA").Select
        ActiveWindow.SelectedSheets.Visible = False
       
        Sheet8.Select
        Range("G15").Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
       
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True
               
    End Sub

    Thanks
    Tuesday, March 10, 2015 1:48 PM

All replies

  • At the top of your module, declare a public variable:

       Dim NextTime As Date

    Then, at the and of your Sub, use

        NextTime = Now() + TimeValue("00:20:00")
        Application.OnTime NextTime, "RESFRESHCUSTOMERORDERS", Schedule:=True

    But you also need to use this in the before close event of the Thisworkbook codemodule:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

        CancelRefresh

    End Sub


    where you have this procedure in a standard codemodule to cancel the refresh:

    Sub CancelRefresh()

        Application.OnTime NextTime, "RESFRESHCUSTOMERORDERS", Schedule:=False

    End Sub


    Tuesday, March 10, 2015 2:24 PM