Run Macro on Monday when workbook opened

[sean1541]

Hi I want to run a macro when a workbook is opened on a Monday but only run it on the first occasion that it is opened on Monday any help on this would be great

 

[Joe4]

Have the macro capture and store the data in an unused cell somewhere on your worksheets (and immediately save the file).
Then you can have the first step of the macro to check that date. If it is today, then exit the sub and do not go any further.
Otherwise, check to see if it is a Monday or proceed with the rest of your code.

You would use a "Workbook_Open" event procedure, which runs automatically whenever a file is opened.
That code would be structured like this, and MUST be placed in the "ThisWorkbook" module is in order to run automatically upon opening of the file:

Private Sub Workbook_Open()

'   Check to see if data in cell Z1 on Sheet1 is today, and if so, exit
    If Sheets("Sheet1").Range("Z1") = Date Then Exit Sub
   
'   Exit if not currently a Monday
    If Weekday(Date) <> 2 Then Exit Sub
   
'   Run your code below
'   ...

'   Update date in cell Z1 on Sheet1 and save file
    Sheets("Sheet1").Range("Z1") = Date
    ActiveWorkbook.Save
   
End Sub

 

[sean1541]

Thanks for your help
Regards
Sean.

 

[Joe4]

You are welcome.
Glad I was able to help!