To choose several sheets for synchronizing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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