Macro to run at time interval

[ziggyfo]

Hi Guys,

I have this basic code that works when run manually, but the code that is supposed to run this every x seconds doesn't seem to work.

I have this code in a module

Sub Odds()

RunTimer = Now + TimeValue("00:00:05")

Application.OnTime RuntTimer, "Odds"
With Sheets("Sheet2")
.Range("A3").Copy Destination:=.Range("H" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub




In the workbook code i have


Private Sub Workbook_Open()

Call Odds

End Sub


Could anyone assist me with why this isn't running at the specified interval?

 

[JoeMo]

Hi Guys,

I have this basic code that works when run manually, but the code that is supposed to run this every x seconds doesn't seem to work.

I have this code in a module

Sub Odds()

RunTimer = Now + TimeValue("00:00:05")

Application.OnTime RuntTimer, "Odds"
With Sheets("Sheet2")
.Range("A3").Copy Destination:=.Range("H" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub




In the workbook code i have


Private Sub Workbook_Open()

Call Odds

End Sub


Could anyone assist me with why this isn't running at the specified interval?

Maybe the typo in red is causing the problem.

 

[ziggyfo]

Maybe the typo in red is causing the problem.

good spotting Joe!

 

[ziggyfo]

Joe you wouldn't perhaps know how to adjust the code to paste values only?

 

[JoeMo]

Try:

With Sheets("Sheet2")
.Range("A3").Copy
.Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlValues
End With

Joe you wouldn't perhaps know how to adjust the code to paste values only?