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

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

Hi, Joel, maybe I couldnt quite follow your advice. But I just want to make
30 target sheets to do the same thing as I intrigued the macro.
When I just put these codes in a the module and run the macro, it only
worked for the activated sheet and the others dont work.

Hope you can help me with this

"Joel" wrote:

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

Have all worksheets use the same cell on the same worksheet. I thought the
timer ws common for all the worksheets. To get everything perfectly
synchronized, you should have one time lauch all the macros.

if Sheets("Sheet1").Range("A1") = 5 then exit sub

"Jonathan" wrote:

Hi, Joel, maybe I couldnt quite follow your advice. But I just want to make
30 target sheets to do the same thing as I intrigued the macro.
When I just put these codes in a the module and run the macro, it only
worked for the activated sheet and the others dont work.

Hope you can help me with this

"Joel" wrote:

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