Using a countdown timer in a cell to run a macro

[nightracer]

Hi there

At various times during the day I need to take a snapshot of data totals in a spreadsheet.

I have a macro set to simply pick up the values in the range I need (H9:I60) and copy them as values into AA9:AA60
I also have a countdown timer running in cell F9 to the next delivery point when I need the snapshot of these totals that are formula based.

Can I use the countdown timer hitting 00:00:00 to run the macro?

Any help appreciated, thanks
N

 

[nightracer]

That should be AA9:AB60 this first time I take the snapshot, then AD9:AE60 the next and then AG9:AE60 etc.

 

[Logit]

.

Option Explicit


Sub shwMsg()
'The next line TimeValue sets the macro to execute every five seconds. Adjust time as required: hh:mm:ss = 00:00:05
    Application.OnTime Now + TimeValue("00:00:05"), "shwMsg"
    MsgBox "Hi !", vbOKOnly, "What ?"
End Sub

Paste the above macro into a Routine Module. You can initiate the macro either through a Command Button on the worksheet or by 'calling' it from the ThisWorkbook module in the OPEN event.

Delete this line from the above macro : MsgBox "Hi !", vbOKOnly, "What ?" and replace it with the name of your macro that performs the copying.

Adjust the time to comply with your needs. TimeValue("00:00:05"), "shwMsg" <---- presently fires the macro every 5 seconds. If you wanted the macro to execute every hour, it would be
TimeValue("01:00:00"), "shwMsg".

Hope that helps.