Software

Ask something
Discussion Locked

excel macro help

0 helpful
9 replies

by hotrodgers

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

how can i automatically update a spreadsheet once each day..i have a macro "accumulator" that i want to run once a day at 6pm ..i have tried this below:

Sub SetRunTime()
Application.OnTime TimeValue ("12:00:00pm"), "Accumulator"
End Sub
Sub Accumulator()
Worksheets(3).Range("f1:f1000").Copy
Worksheets(3).Range("g1:g1000").PasteSpecial Paste:=xlValues, Operation:=xlAdd
End Sub

but it will not work, i leave the spreadsheet open but it still don't update...i can update manually, but i need it to update automatically and preferably only once a day as a security in case someone tries to run again the same day manually...any suggestions?

9 replies

by gav !!

'This runs Accumulator at 12 P.M.

Application.OnTime TimeValue("12:00:00"), "Accumulator"

'This cancels Accumulators OnTime setting.

Application.OnTime EarliestTime:=TimeValue("12:00:00"), _
Procedure:="my_Procedure", Schedule:=False

by gav !!

'This runs Accumulator at 5 P.M.

A fix on previous

Application.OnTime TimeValue("12:00:00"), "Accumulator"

'This cancels Accumulators OnTime setting.

Application.OnTime EarliestTime:=TimeValue("12:00:00"), _
Procedure:="Accumulator", Schedule:=False

by gav !!

For the whole thing

try this......

Public WhatTime As Double

Sub StartIt()
WhatTime = Now + TimeValue("06:00:00")
Application.OnTime EarliestTime:=WhatTime, Procedure:="Accumulator"
End Sub

Sub Accumulator()

Worksheets("sheetwhatever").Select
Range("F1:F1000").Copy
Range("F1:F1000").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

WhatTime = Now + TimeValue("06:00:00")
Application.OnTime EarliestTime:=WhatTime, Procedure:="Accumulator"
End Sub

Sub StopIt()
Application.OnTime EarliestTime:=WhatTime, Procedure:="Accumulator", _
Schedule:=False
End Sub

This discussion is closed to new replies.