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
> > >