Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hope to stock data at every single update&all the sheets stocking synchronized
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hope to stock data at every single update&all the sheets stocking
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" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hope to stock data at every single update&all the sheets stock
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" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hope to stock data at every single update&all the sheets stock
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" wrote: 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" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hope to stock data at every single update&all the sheets stock
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hope to stock data at every single update&all the sheets stock
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. "Jonathan" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
Multiple sheets as data for a single sheet | Excel Worksheet Functions | |||
Easy one (I hope) Using Sheet Names to reference sheets in other Workbooks | Excel Programming | |||
Keeping multiple sheets synchronized | Excel Programming | |||
Print single sheets from rows of data | Excel Programming |