Macro to run at time interval

ziggyfo

New Member
Joined
Mar 24, 2021
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,511
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Solution

ziggyfo

New Member
Joined
Mar 24, 2021
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Joe you wouldn't perhaps know how to adjust the code to paste values only?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,511
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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?
 

Forum statistics

Threads
1,140,929
Messages
5,703,225
Members
421,285
Latest member
Bebek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top