Running a macro the last business day

[jonc31]

Can someone show me the code I would need if I open a spreadsheet automatically everyday, but if the macro is open on the last business day of the month I want it to call other sub procedures.

Thanks

 

[HalfAce]

I'm not sure exactly what you're asking for.
To test if it is the first of the month when the file opens, you can do something as simple as:

If Day(Now) = 1 Then
  MsgBox "Today is the first of the month"
Else
  MsgBox "Not the first today."
End If

obviously replacing the message boxes with your own code.

What I'm not sure about is, do you already have the code to open the file automatically? - or do you need help with that as well?

Hope it helps.

 

[jonc31]

I am going to use the windows task scheduler to open the fiel everyday at 6 am. I need code that will call other sub procedures only if its the last business day of the a month.

 

[HalfAce]

This should be a good place to start.
I've done several tests on it and it seems to be working well but I'm not sure if I've covered every conceivable scenario.

You might want to try something like this (in the ThisWorkbook module) and see where it gets you.

Private Sub Workbook_Open()
Dim x As Date, m As Integer, y As Integer
x = Int(Now())
m = Month(x)
y = Year(x)

If m = 12 Then
  LastDayOfThisMonth = DateValue(m & "/" & 31 & "/" & y)
Else
  LastDayOfThisMonth = DateAdd("d", -1, DateValue(m + 1 & "/" & 1 & "/" & y))
End If

LastDay = Weekday(LastDayOfThisMonth)
Select Case LastDay
  Case 1
    LastWeekDay = Month(LastDayOfThisMonth) & "/" & Day(LastDayOfThisMonth) - 2 & "/" & Year(LastDayOfThisMonth)
  Case 2 To 6
    LastWeekDay = Month(LastDayOfThisMonth) & "/" & Day(LastDayOfThisMonth) & "/" & Year(LastDayOfThisMonth)
  Case 7
    LastWeekDay = Month(LastDayOfThisMonth) & "/" & Day(LastDayOfThisMonth) - 1 & "/" & Year(LastDayOfThisMonth)
End Select

If LastWeekDay = Date Then
  MsgBox "Today is the last week day of this month."
  '''Your code for the last week day of this month goes here
Else
  MsgBox "The last week day of this month is " & LastWeekDay
  '''Your code (if any) for every other day of the month goes here
End If

End Sub

Hope it helps.