Automatically calling Macros

Elkash

New Member
Joined
May 3, 2012
Messages
12
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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.

Rich (BB code):
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

New Member
Joined
May 3, 2012
Messages
12
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.

Rich (BB code):
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

New Member
Joined
May 3, 2012
Messages
12

ADVERTISEMENT

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

I'll let you know what happens on Friday.

Shaun.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

New Member
Joined
May 3, 2012
Messages
12
Thanks for the update. Not sure why I was convinced that Excel started the week on a Monday. Anyway, changed to 6 now.

Thanks :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,315
Messages
5,600,908
Members
414,415
Latest member
joshuaba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top