Loggind cell entries in a list, 10 times, on a time interval

[mcphatt]

Hi all,

I have quite a specific problem to solve. Searching the forums I have found two solutions to the three aspects I need.

First, I need to have a macro that will copy a cell entry every time the cell changes into a column on another sheet. I have found the following code which sort of works, although it loops through the calculation then stops and lists all the entries, when I want only 1:

Private Sub Worksheet_Calculate()
With Sheets("LogSheet")
lr = .Cells(Rows.count, "A").End(xlUp).Row
If Range("D21").Value <> .Cells(lr, "A") Then
.Cells(lr + 1, "A").Value = Range("D21").Value
End If
End With
End Sub

I also want the macro to calculate every 30 seconds, for which I have this code:

Sub SaveThis()
Application.DisplayAlerts = False
ActiveSheet.Calculate
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:00:30"), "SaveThis"
End Sub

Which works, but I don't have a macro to stop it, and more precisely I want it to calculate every 30 seconds only ten times at a time.

Is there anyway to put all three criteria into one code - the cell logging in a list, calculate every 30 seconds for ten calculations, so that I end up with ten entries in a list on my log sheet, all calculated at the thrity second interval?

Thanks for your help in advance.

Matt

 

[mcphatt]

Hey all,

Now realised that the time event macro will just run and run, and even reopen my workbook, can anyone explain how to stop it?

Thanks!