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

The attach file button doesn't work - click "Go Advanced" to the lower right of the edit window, then use the "Manage attachments" option.

Save your file as a macro-enabled .xlsm and change "SheetName" in two places in the code to the actual sheet name.

With this in a regular code module:

Option Explicit
Dim NextTime As Date

Sub ScheduleRun()
    'Schedule for 1600
    NextTime = Date + 1600 / 2400
    Application.OnTime NextTime, "RunMe"
End Sub

Sub RunMe()
    Dim shW As Worksheet
    Set shW = ThisWorkbook.Worksheets("SheetName")
    
    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

Sub StopMe()
    'Un-schedule for today...
    Application.OnTime NextTime, "RunMe", Schedule:=False
End Sub

And this in the codemodule of the ThisWorkbook object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim shW As Worksheet
    Set shW = ThisWorkbook.Worksheets("SheetName")
    
    If shW.Cells(shW.Rows.Count, "A").End(xlUp).Value = Date Then Exit Sub
    StopMe
    RunMe
End Sub

Private Sub Workbook_Open()
    ScheduleRun
End Sub

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?