Macro that AUTOMATICALLY activates another macro without human interference? HELP

Hi Guys.

I've been creating a macro for my (small) investment team, but I'm struggeling to get the final touch to work. The macro just updates certain cells which contains a function that get (almost) real-time quotes from Yahoo finance. When the cells is updated the macro prints the total portfolio value to another sheet along with date and time. Simple as that. The problem is that some of my stocks is bought in another stock exchange which closes at another time of the day. In other words, I have to create a macro that automatically activates the above-mentioned macro at a certain time of the day (23.59). I've tried alot and my macro ONLY works if i press F5 before i leave work, but I want this update to happend every night without me interfering each day. Below you can se all the codes (ps. if you have any tips to make it better I would appriciate it).

Sub knappen()
    Workbooks("Valuation (test)").Activate
    Sheets("MLC").Select
    Range("CADNOK").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""CADNOK=x"")"
    Range("USDNO").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""USDNOK=x"")"
    Range("SEKNOK").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""SEKNOK=x"")"
    Range("Axactor").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""axa.ol"")"
    Range("Bakkafrost").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""bakka.ol"")"
    Range("DNO").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""dno.ol"")"
    Range("Golden").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""gogl.ol"")"
    Range("GoldenReg").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""gogl-r.ol"")"
    Range("Kinross").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""k.to"")"
    Range("Nel").Select
    ActiveCell.FormulaR1C1 = "=StockQuote(""nel.ol"")"
End Sub

Dim req_value As Variant
Private Sub Worksheet_Activate()
req_value = [Markedsverdi]
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim X As Integer
      If [Markedsverdi] <> req_value Then
        With Worksheets("Avkastning")
            lastRow = .Range("A1048576").End(xlUp).Row
            For X = 1 To lastRow
                If IsDate(.Range("A" & X).Value) Then
                    If CDate(.Range("A" & X).Value) = Date Then Exit For
                End If
            Next
            .Range("B" & X).Value = Range("Markedsverdi").Value
            .Range("A" & X).Value = Date
            .Range("D" & X).Value = Time
        End With
    End If
End Sub

Private Sub Workbook_Open()
If Weekday(Now, vbMonday) < 6 Then
    Application.OnTime VBA.TimeValue("23:59:00"), "knappen"
End If
End Sub

I have never written a macro before, so the macro above is creatied through googling and trying/failing.

Best regards
Thomas Mangor-Jensen

Hi Thomas,

Where you've placed all these codes. To work properly some pieces shall be in standard module (like your knappen macro), other in Thisworkbook module (Private Sub Workbook_Open()) and some in a worksheet code (Private Sub Worksheet_Activate() or Change )

The next point is - please edit your post and use code tags - it increases readability of the code and is required by our http://www.excelforum.com/forum-rule...rum-rules.html

Private Sub Workbook_Open()
If Weekday(Now, vbMonday) < 6 Then
    Application.OnTime VBA.TimeValue("23:59:00"), "knappen"
End If
End Sub

shall be in thisworkbook code. In VBA Editor (Alt+F11) click on the list of your workbook objects on ThisWorkbook (could be also translated - in PL versions since Excel 2007 it's called Then_Skoroszyt - which means thisworkbook anyway :-P) to open it's code

PS. To attach a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window. Select files (max 2) press Upload and press Close then Submit reply

As for the OnTime - as you see it is called when workbook is open.
If you closed the file and then opened it system scheduler shall have knappen planned on 23:59 today. But note that if you do not open file again, nothing will be scheduled for monday 23:59, Tuesday 23:59 etc.

Plying with OnTime is always a bit delicate, but you coukld try to include next OnTime call inside knappen itself:

Sub knappen()
Application.OnTime VBA.TimeValue("23:59:00"), "knappen"
If Weekday(Now, vbMonday) < 6 Then
'current body of the macro
    Workbooks("Valuation (test)").Activate
'...

'end of current body
End If
End Sub

the second question - at the moment I do not have time for detailed investigation. May be you simply face some problems with internet connection?

If the file is always open, the code from post #7 once executed once, shall run every day and if it is not weekend, do what is within the body and if it is weekend just schedule next check on next day a minute before midnight.

Probably the algorithm shall be:

check if it is a week day
if so do weekday tasks and schedule next execution
if not then just schedule next execution