Hope to stock data at every single update&all the sheets stocking synchronized

[jonathan]

For the moment I have the following codes:

Sub Update()
Dim myCell As Range
NextTime = Time + TimeValue("00:00:01")
Range("B5:G7").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


Thanks a lot for Bernie Deitrick last time to help me with this. It
worked very well.

1. What used to be is to stock the updated data in every second
frequency. What I hope now is to detect every single update in the
range, and then make the stocking.

2. the present one only works for the activated sheet. I would like to
make several sheets( say sheet1, sheet 2, sheet 3 to synchronizedly
realise the procedure.

Thanks a lot in advance!

 

[Joel]

Jonathan: I just went back and read your posting from the 11. Barb said she
thought you were using a worksheet_change functtion and so do I. Worksheett
change functions only work on one sheet. You must copy the code to each
sheet you are using.

You can havve tthe worksheet change call a common module to do the processing

sub worksheet_change(byvalue Target as Range)

call common_code (Target)
end sub

in the module page
sub common(byval Target as Range)


end sub

 

[Jonathan]

Hi, Joel

Thanks for your help. But excuse me, can u explain to me more about your
idea, say how to integrate into the codes that I posted?

Really appreciate it

 

[Joel]

I was respondingg to your posting saying "What I hope now is to detect every
single update in the range, and then make the stocking." I intepret this to
mean you were going to eliminate the Ontime. To detect a change cell in a
range, you would need to use a worksheet_change function. Because
worksheet_change works only on one sheet, I was recommending making common
code.

Sub worksheet_change(ByVal Target As Range)
Application.EnableEvents = False

Call common_code(Target)
Application.EnableEvents = True

End Sub

Sub common_code(ByVal Target As Range)
If (Target.Row >= 5) And (Target.Row <= 7) And _
(Target.Column >= 2) And (Target.Column <= 7) Then

Range("B5:G7").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
End If
End Sub

 

[Jonathan]

Dear Joel:

Since the first reply from Barb, I already eliminated the use of change
event. Because my updated data are DDE data, and the formula doesnt change.
only the value of the data changes very freauently.

So I tried, and nothing detected.

Or is there anyway to detect the change of value, I mean any change of value
in the range?

Thanks a lot

 

[Joel]

I would follow Barb's advise. Not every type change on a worksheet triggers
events. For example color changes do not cause worksheets to get updated.