a macro to collect data on a loop every minute.

[dynamicsoul]

Hi
I have created a query that collects new stock data every minute from a
web page.

I need to create a macro that collects data from sheet 1, cells F21,F22
every minute, after refresh, and copies each new data value to columns
A+B on sheet2...I.E F21 copied to A2, F22 copied to B2....then after
refresh F21 copied to A3,F22 copied to B3 and so on etc...until I stop
the macro looping.........

This is so I can plot a live stock chart from the data ranges over
time.........

With a bit of research and advice this is what I tried.......
Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub


Sub The_Sub()

Sub CopyOneArea()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("F21").Resize(2,1)
Set destrange = Sheets("Sheet2").Range("A" & Lr)
sourceRange.Copy destrange

StartTimer


BUT.....as a Macro/VBA novice I havent a clue why its not
working............
Can anyone help me out?
Thanks in advance......

 

[Thomas Lutz]

Try the following:


Public TimerEnabled As Boolean

Sub StartStopTimer()
' run this sub to turn the timer on and off
TimerEnabled = Not TimerEnabled ' toggle on or off

If TimerEnabled Then CopyOneArea

End Sub




Sub CopyOneArea()

' quit if TimerEnabled = False
If Not TimerEnabled then Exit Sub

' run again in 5 seconds - change to whatever interval you need
Application.OnTime Now + TimeValue("00:00:05"), "CopyOneArea"

' do your thing
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("F21").Resize(2,1)
Set destrange = Sheets("Sheet2").Range("A" & Lr)
sourceRange.Copy destrange

End Sub