with using ontime i want to activiate macro at every month end

Question

with using ontime i want to activiate macro at every month end . in this i can put day as 31 but what about month which has day 30 or like feb month . If Day(Now) = 1 Then Application.ontime TimeValue("6:30:00"), "monthly" End If

Answer 1

So, I would propose the next approach. Otherwise, somebody must work on the file and fier a specific event:

Create some events able to call the function you need for next day at a specific time (06:30:00):

Private Sub Workbook_Open()
   Application.OnTime TimeSerial(6, 30, 0), "DoSomething_Eomonth"
End Sub
Private Sub Workbook_SheetActivate(ByVal sh As Object)
   Application.OnTime TimeSerial(6, 30, 0), "DoSomething_Eomonth"
End Sub

Be sure that in the previous days the workbook in discussion is opened and (maybe) some work has been done on it. It must stay open.

The daily called recursive Sub will look like the next one:

Sub DoSomething_EndOfMonth()
  If Day(Date + 1) = 1 Then
    'Do what is to be done
  Else
     Application.OnTime TimeSerial(6, 30, 0), "DoSomething_Eomonth"
  End If
End Sub