Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If statement within macro? (Excel 2000)

    Hello,
    I'm creating 4 sheets that will be used by 4 different users that will feed into one master sheet that I will be checking. In order for the master sheet to stay current, the 4 smaller sheets need to be saved. I'm trying to build a macro to auto save the sheets hourly.

    If A2>NOW()+60
    ActiveWorkbook.save
    End If

    End Sub

    Any help would be terrific. And if you can think of an easier way, I'm open! [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement within macro? (Excel 2000)

    1- How is that code being run? That code will only save the workbook if it is in some macro that is actually run sometime after the save time has been reached.

    2- I also don't know what is in A2, so I can't guess what is going on.

    3- Now() + 60 is the current time plus 60 days. I don't think that is what you had in mind.

    I think that what you want is something like the following. First, put something like the following in the workbook open event routine (right click on ThisWorkbook in the VBE Project Explorer and then click on View Code in the Popup Menu. Click on Workbook in the left drop down list, then on Open in the right drop down list).

    <code>
    Private Sub Workbook_Open()
    Application.OnTime Now() + TimeSerial(0, 60, 0), "SaveWorkbook"
    End Sub
    </code>

    Now put something like this in a normal module:

    <code>
    Public Sub SaveWorkbook()
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    Application.OnTime Now() + TimeSerial(0, 60, 0), "SaveWorkbook"
    End Sub
    </code>

    I did not test this code, so there may be errors in it.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •