About my looping
 

Just started VB for some functions that I would love to realise. but
the onlyu thing that I have for the moment is

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/07/2007 by STD03
'

'
i = 0
Do While Now() 39269
Range("B5:G7").Select
Selection.Copy
Range("B13").Select
ActiveCell.Offset(i, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
i = i + 3
Loop
End Sub

B5:G7 is some dynamic data from other database which change
irregularly. my code makes it copy and paste in a enormous frequency.
what i want to realise is to make it copy every 5 seconds, in order to
sotck the data on a regular base.

just started VB learning and I have a very short period to make this
realised. So hope I can get some help.

Thanks a lot in advance

About my looping
 

Jonathan,

You would be better off using the OnTime method to control the data write.

Use the code below, and run StartIt to start the process, and StopIt to stop the process. This will
put the date and time into column A next to the stored values for record-keeping.

HTH,
Bernie
MS Excel MVP


Dim NextTime As Date

Sub StartIt()
Update
End Sub

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

Sub StopIt()
On Error Resume Next
Application.OnTime NextTime, "Update", schedule:=False
End Sub



"jonathan" wrote in message
ps.com...
Just started VB for some functions that I would love to realise. but
the onlyu thing that I have for the moment is

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/07/2007 by STD03
'

'
i = 0
Do While Now() 39269
Range("B5:G7").Select
Selection.Copy
Range("B13").Select
ActiveCell.Offset(i, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
i = i + 3
Loop
End Sub

B5:G7 is some dynamic data from other database which change
irregularly. my code makes it copy and paste in a enormous frequency.
what i want to realise is to make it copy every 5 seconds, in order to
sotck the data on a regular base.

just started VB learning and I have a very short period to make this
realised. So hope I can get some help.

Thanks a lot in advance

About my looping
 

On Jul 11, 3:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Jonathan,

You would be better off using the OnTime method to control the data write.

Use the code below, and run StartIt to start the process, and StopIt to stop the process. This will
put the date and time into column A next to the stored values for record-keeping.

HTH,
Bernie
MS Excel MVP

Dim NextTime As Date

Sub StartIt()
Update
End Sub

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

Sub StopIt()
On Error Resume Next
Application.OnTime NextTime, "Update", schedule:=False
End Sub

"jonathan" wrote in message

ps.com...



Just started VB for some functions that I would love to realise. but
the onlyu thing that I have for the moment is

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/07/2007 by STD03
'

'
i = 0
Do While Now() 39269
Range("B5:G7").Select
Selection.Copy
Range("B13").Select
ActiveCell.Offset(i, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
i = i + 3
Loop
End Sub

B5:G7 is some dynamic data from other database which change
irregularly. my code makes it copy and paste in a enormous frequency.
what i want to realise is to make it copy every 5 seconds, in order to
sotck the data on a regular base.

just started VB learning and I have a very short period to make this
realised. So hope I can get some help.

Thanks a lot in advance- Hide quoted text -

- Show quoted text -

Thanks so much.
it works perfectly so far.

About my looping
 

Sorry to hear about your looping