Stock Exchange Data Log

[BuffaloGuy]

I am attempting to log stock data every 'n' minutes (1 in this example), I bring in the information via data in excel data types.
This code is supposed to refresh data every minute and copy the data from cells C3 through I3 and copy it into row 103 and keep the data and add the new data on top.

I copied this code from someone else and tried to modify it to my needs. I likely butchered it.

I get a application-defined or object-defined error message.

Sub StartLogging()
  Const strSOURCE_SHEET = "Data" ' <-- name of sheet containing stock data
  Const strTARGET_SHEET = "Data" ' <-- name of sheet to periodically log data to
  Const strSOURCE_RANGE = "C3:I3" ' <-- cell address of data to be logged
  Dim intNextRow As Integer
  Dim rngSource As Range
 
  On Error GoTo ErrorHandler
  Application.ScreenUpdating = False
  Set rngSource = ThisWorkbook.Sheets(strSOURCE_SHEET).Range(strSOURCE_RANGE).Rows(1)
  With ThisWorkbook.Sheets(strTARGET_SHEET)
    intNextRow = .Cells(103, .Rows.Count).End(xlDown).Row + 1
    .Cells(103, intNextRow).Value = Now()
    .Cells(103, intNextRow).Font.Bold = True
    .Cells(104, intNextRow).Resize(rngSource.Cells.Count).Value = rngSource.Value
    .Rows(intNextRow).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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Logit]

The following will perform the desired function. There must be something in Cell A102 for this method to begin copying to cell
A103.

Option Explicit

Dim TimeToRun


Sub MacroRun()

    TimeToRun = Now + TimeValue("00:00:05")
    Application.OnTime TimeToRun, "Macro1"
    
End Sub

Sub Macro1()
    Calculate
    Worksheets("sheet1").Range("C3:I3").Copy
    
    Worksheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Application.CutCopyMode = False

MacroRun

End Sub

Sub stopMacros()

    On Error Resume Next
    Application.OnTime TimeToRun, "Macro1", , False
    
End Sub

 

[BuffaloGuy]

Thank you so much. This will work much better. Its working pretty well, but it copies the data to cell A2:G2
Thoughts?

 

[Logit]

There must be something in Cell A102 for this method to begin copying to cell
A103.

 

[BuffaloGuy]

I needed it to work in column C, thats why it didn't work. I figured that part out.

Many Thanks!!

 

[Logit]

Worksheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[BuffaloGuy]

Yeah that's what I did.
Will the MacroRun refresh the the worksheet on the same interval?

 

[Logit]

"Will the MacroRun refresh the the worksheet on the same interval?"

No. You can add that.