Sub StartLogging()
Const strSOURCE_SHEET = "Stock Data" ' <-- name of sheet containing stock data
Const strTARGET_SHEET = "Data Log" ' <-- name of sheet to periodically log data to
Const strSOURCE_RANGE = "C3:I3" ' <-- cell address of data to be logged
Dim intNextCol As Integer
Dim rngSource As Range
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Set rngSource = ThisWorkbook.Sheets(strSOURCE_SHEET).Range(strSOURCE_RANGE).Columns(1)
With ThisWorkbook.Sheets(strTARGET_SHEET)
intNextCol = .Cells(5, .Columns.Count).End(xlToLeft).Column + 1
.Cells(5, intNextCol).Value = Now()
.Cells(5, intNextCol).Font.Bold = True
.Cells(6, intNextCol).Resize(rngSource.Cells.Count).Value = rngSource.Value
.Columns(intNextCol).AutoFit
End With
m_dtmNextSchedule = Now() + TimeValue("0:01") ' <-- reschedule for 1 minute time
Application.OnTime m_dtmNextSchedule, "StartLogging", Schedule:=True
ExitHandler:
On Error Resume Next
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Sub StopLogging()
On Error GoTo ErrorHandler
Application.OnTime m_dtmNextSchedule, "StartLogging", Schedule:=False
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbExclamation
End Sub