Capture value from a dynamic cell at a specific Hour and populate a column

You put the event code into a standard codemodule rather than the Thisworkbook object's codemodule: I have attached a working version with one change that I made to make testing easier: instead of 1200/2400 I use TimeSerial(12,0,0) (if you wanted to run at 11:47 AM, you would not use 1147/2400: TimeSerial(11,47,0) will allow you to choose that as the exact time.

Change RunMe to this to address the multiple saves:

Sub RunMe()
    Dim shW As Worksheet
    Set shW = ThisWorkbook.Worksheets("SheetName")
    If shW.Cells(shW.Rows.Count, "A").End(xlUp).Value = Date Then Exit Sub
    shW.Cells(shW.Rows.Count, "A").End(xlUp)(2).Value = Date
    shW.Cells(shW.Rows.Count, "A").End(xlUp)(1, 2).Value = shW.Range("A1").Value
End Sub

The timing / schedule works for me: are you opening the workbook once in the morning and closing it after the time that you want to store the data?