Automatically calling Macros

[Elkash]

Guys,

Basically I'm wanting to have 3 macros automatically fired at set times each morning (Workbook is already open at these times). However, if it's a Friday, I need an additional Macro to be fired, followed by the remaining daily ones. I need to know if my code is likely to be correct, but can't wait until Friday

Private Sub Workbook_Open()
'
Worksheets("HOME").Activate
'
If Weekday(Date) = 5 Then
Application.OnTime TimeValue("01:50:00"), "WeekClose"
Else
Application.OnTime TimeValue("02:00:00"), "IMPORT_Overview"
Application.OnTime TimeValue("02:30:00"), "Email_Import"
Application.OnTime TimeValue("03:00:00"), "Export_Top_Level"
End If
'
End Sub


Thanks for any reassurance.

Shaun.

 

[dave3009]

I think if you want the three to run everyday and just the one additional one on the Friday (four in total) then you need.

Private Sub Workbook_Open()
'
Worksheets("HOME").Activate
'

Application.OnTime TimeValue("02:00:00"), "IMPORT_Overview"
Application.OnTime TimeValue("02:30:00"), "Email_Import"
Application.OnTime TimeValue("03:00:00"), "Export_Top_Level"

If Weekday(Date) = 5 Then
Application.OnTime TimeValue("01:50:00"), "WeekClose"
End If
'
End Sub

 

[Elkash]

Thanks for the quick response Dave. What you've said makes perfect sense, and had crossed my mind, but my concern is the particular times I need these to run. On a Friday, I'd need the WeekClose macro to fire first. Would this be the case, with the adjustments you've made?

Many thanks again.
Shaun.

I think if you want the three to run everyday and just the one additional one on the Friday (four in total) then you need.

Private Sub Workbook_Open()
'
Worksheets("HOME").Activate
'

Application.OnTime TimeValue("02:00:00"), "IMPORT_Overview"
Application.OnTime TimeValue("02:30:00"), "Email_Import"
Application.OnTime TimeValue("03:00:00"), "Export_Top_Level"

If Weekday(Date) = 5 Then
Application.OnTime TimeValue("01:50:00"), "WeekClose"
End If
'
End Sub

 

[dave3009]

Hi Shaun

I should have read the brief a bit more thoroughly, if you just move the if statement above the three usual macros you're good to go.

KR

Dave

 

[Elkash]

Thanks Dave. Valuable help, much appreciated, as always from MrExcel

I'll let you know what happens on Friday.

Shaun.

 

[dave3009]

Glad to help, one little caveat...the weekday number for Friday is 6, so either change this or let me know if it fired OK on Thursday.

 

[Elkash]

Thanks for the update. Not sure why I was convinced that Excel started the week on a Monday. Anyway, changed to 6 now.

Thanks

 

[dave3009]

You can set the weekday function to act the way you want it but seeing as VBA and dates don't mix very well you're best sticking with the default. Here's a handy link

http://www.techonthenet.com/excel/formulas/weekday.php