auto run macros just STOPS

    Hi Ho .. Hi Ho ..


    [vba]
    Private Sub Workbook_Open()

    Run "auto_save"
    Application.OnTime Now + TimeValue("00:10:00"), "auto_save"

    End Sub[/vba]


    I have this running in this workbook .. workbook 'a'. Workbook 'a' collects information from 2 other workbooks 'b' & 'c'. When 'a' opens, the macro runs perfectly and runs every 10 minutes.


    BUT .. after it has opened and new data is entered into either 'b' or 'c', this macros ceases to run. Any reason why this would stop?


    I have included the macro 'auto_save' for reference


    [vba]
    Sub auto_save()
    '
    ' auto_save Macro
    ' Macro recorded 6/01/2006
    '
    ' This runs an auto save and auto sort
    '
    ActiveWorkbook.Save

    Range("B7:R607").Select
    Selection.sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B7").Select

    Range("B7:BD106").Select
    Selection.sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B7").Select

    Range("B7:AW106").Select
    Selection.sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B7").Select

    End Sub[/vba]


    Hope someone can solve why this macro stop running if new data is updated from 'b' and 'c'.

    Tayler [the blue fox] :thanx:

    Re: auto run macros just STOPS


    Hi,


    Without seeing an example of your workbook I'm not sure I can help BUT:


    try putting the ontime statement at the end of your save macro:


    [vba]
    Sub auto_save()
    '
    ' auto_save Macro
    ' Macro recorded 6/01/2006
    '
    ' This runs an auto save and auto sort
    '
    ActiveWorkbook.Save

    Range("B7:R607").Select
    Selection.sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B7").Select

    Range("B7:BD106").Select
    Selection.sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B7").Select

    Range("B7:AW106").Select
    Selection.sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B7").Select

    'Add this here:
    Application.OnTime Now + TimeValue("00:10:00"), "auto_save"


    End Sub


    [/vba]


    You could also try a standard errror handler routine:


    [vba]
    Private Sub Workbook_Open()

    On Error GoTo ErrorHandler

    ' Run "auto_save"
    Application.OnTime Now + TimeValue("00:01:00"), "auto_save"
    Exit Sub


    ErrorHandler:
    strMsg = "Error number is: " & Err.Number & vbCrLf
    strMsg = strMsg & "Error message is: " & Err.Description & vbCrLf
    strMsg = strMsg & "Error source is: " & Err.Source
    MsgBox strMsg, vbOKOnly + vbCritical, "Error"


    End Sub



    [/vba]


    Use the error handler routine in any code.


    PS my responding will also bump this message. Good luck!


    Cheers,


    dr

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login