Make a record of a excel cell

scottappleford

Registered User.
Local time
Today, 02:43
Joined
Dec 10, 2002
Messages
134
Hi

I have a formula constantly calculating in Cell M3

I would like to make a record of this every 30 seconds in column P

Thanks
 
you mean ,
copy the current value in M3 to another page
then repeat every 30 secs?
 
hi

yes this is correct please

it copies the content of M3 (this is calculating based on a fomula) and copies it to column P is fine and keep a history - so copies to next available cell in column P - can start at Cell P1

many Thanks
 
Hi

sorry to be pain - can anyone help me with this one please

appreciate your help
 
This is just from memory
Look up the Application.OnTime for Excel
It is somewhat like an Alarm Clock
In a Worksheet module - add the code.
Place the sstartTime procedure in the Auto_Open event
code something like this:
Code:
Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:03"), "Timer" 'ever 3 seconds
End Sub

Private Sub Stop_Timer()
    TimerActive = False
End Sub

Private Sub Timer()
    If TimerActive Then
        ActiveSheet.Cells(1, 1).Value = Time
        Application.OnTime Now() + TimeValue("00:00:03"), "Timer"
    End If
End Sub

Your Excel must be saved as a Macro Enabled
The knowledge of the Debugging Tools will be very important
The management of these events will be tricky!
1 trying to code and the focus on the window gets interrupted every time the event triggers.
2 When multiple workbooks open, you close the one that's supposed to use the timer, and it keeps triggering and reopening the workbook (forgetting to kill the event properly).

Suggest testing every 30 seconds until the code works correctly
 

Users who are viewing this thread

Back
Top Bottom