This is a very crude method, but it works. Test a cell location. You can
make it fancy by adding a button that writes to the cell.

With myCell.Offset(0, -1).Resize(3)
.Value = Now
.NumberFormat = "mm/dd/yy hh:mm:ss"
End With
if Range("A1") = 5 then exit sub
Application.OnTime NextTime, "Update"
End Sub

"Jonathan" wrote:

Hi, I have a problem when implementing my coding:

I am using Ontime Method to record data from a dynamic datasource. For
better use, I organise at least 30 sheets to use this method for the same
range A1:A10, every sheet same place but different data. I wonder a way to
initiate them and stop them at the same time.

the codes are following:

Sub Update()
Dim myCell As Range
NextTime = Time + TimeValue("00:00:01")
Range("A1:A10").Copy
Set myCell = Cells(Rows.Count, 2).End(xlUp)(2)
myCell.PasteSpecial _
Paste:=xlPasteValues
With myCell.Offset(0, -1).Resize(3)
.Value = Now
.NumberFormat = "mm/dd/yy hh:mm:ss"
End With
Application.OnTime NextTime, "Update"
End Sub

When i only put this in Module, it excuted only for the activated sheet.
Hope to excute 30 sheets that I want no matter which sheet I activate.

But for the rest of the sheets, I do not want this procedure realised.

Thanks in advance