Storing automatically updated data

[Bill]

I am looking for some help storing data that is automatically updated. I
recieve stock price information in Excel and want to customize it. I want to
store the high and the low for a given time period and have tried using the
following loop (my comments are noted in { }):
Sub Data_Test()
Worksheets("Strategy").Activate
Range("I54").Calculate {This is the price quote copied from another
sheet}
Range("I54").Select
Selection.Copy
Range("I55").Select
Selection.PasteSpecial Paste:=xlPasteValues {storing the initial price
to determine the high later}
Range("J55").Select
Selection.PasteSpecial Paste:=xlPasteValues {storing the initial price
to determine the low later}
Application.CutCopyMode = False
Do
Worksheets(5).Range("I54").Calculate {want to refresh data but
doesn't work during loop}
If Worksheets(5).Range("I54").value >
Worksheets(5).Range("I55").value Then
Worksheets("Strategy").Activate
Range("I54").Select
Selection.Copy
Sheets("Strategy").Select
Range("I55").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
If Worksheets(5).Range("I54").value <
Worksheets(5).Range("J55").value Then
Worksheets("Strategy").Activate
Range("I54").Select
Selection.Copy
Sheets("Strategy").Select
Range("J55").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Stop
Loop Until Time = TimeValue("10:48:00 AM")
End Sub

I believe the code is correct. However, the link for the external data does
not update when the loop is running. Is there any way to store the data that
is generated so I can determine the high and low prices between a certain
time frame? The method outlined above seems cumbersome, there must be a
better option.

Thanks,

 

[Don Guillett]

Your code could stand major revision such as

Range("I54").Select
Selection.Copy
Range("I55").Select
Selection.PasteSpecial Paste:=xlPasteValues

could be

Range("I55").value=Range("I54").value

but your problem is probably that you query is not waiting. You can do your
updating via macro with a .Refresh BackgroundQuery:=False line in the query
and then use a macro to get the values desired.