Excel-Copy paste Data download from internet

Microsoft Excel 2007
July 2, 2009 at 09:44:49
Specs: Windows Vista
I am downloading S&P500 data from google
finance in my excel, using automatic refresh,
with a frequency of 1 refresh per minute.
Challenge is: I want to keep a record of 10
minute prices by copy pasting this data in a
column in the same worksheet. How do I write
a macro, which copies the cell which is getting
automatic refreshed, and pastes its value in
another column in the same worksheet. please

See More: Excel-Copy paste Data download from internet

Report •

July 2, 2009 at 16:58:31
Try this...

The assumption is that A2 is the cell that gets updated and that B2 is available to be used as a counter.

I ran with a worksheet that does a query out to the World Clock website and downloads the current time every minute. Every 10 minutes it copied the time to the next empty column in the sheet.

Buy low..Sell high!

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if A2 has changed
 If Target.Address = "$A$2" Then
'If yes, then check if counter is less than 10
  If Range("B2") < 9 Then
'If yes, then increment counter
   Range("B2") = Range("B2") + 1
'If B2 = 9, then copy Column A to the 
'next empty column and reset counter
      Cells(1, Cells(1, Columns.Count). _
      End(xlToLeft).Column + 1).PasteSpecial (xlPasteValues)
      Range("B2") = 0
  End If
 End If
End Sub

Report •
Related Solutions

Ask Question