Macro for copying the value of one Cell to another cell(s) in specific time interval

[mdmackillop]

Give this a try. Note that I had to make the MntCalculate time prior to the Open time to get this to work.
Test workbook attached.
Note: Your Day function is incorrect, Weekday is required.

Sub MntCalculate()
    LC = Application.Max(11, Cells(2, Columns.Count).End(xlToLeft).Column + 1)
    For i = 2 To 218
        Cells(i, LC) = Range("J" & i)
    Next i
    If Weekday(Date) <> 1 And Weekday(Date <> 7) Then
        If TimeValue(Now) > TimeValue("08:59:00") And TimeValue(Now) < TimeValue("15:30:00") Then
            Application.OnTime Now + TimeValue("00:01:00"), "Sheet1.MntCalculate"
        End If
    End If
End Sub




Private Sub Workbook_Open()
    If Weekday(Date) <> 1 And Weekday(Date) <> 7 Then
        Application.OnTime TimeValue("09:00:00"), "Sheet1.MntCalculate"
    End If
End Sub