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 help

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 Else 'If B2 = 9, then copy Column A to the 'next empty column and reset counter Range("A1").EntireColumn.Copy Cells(1, Cells(1, Columns.Count). _ End(xlToLeft).Column + 1).PasteSpecial (xlPasteValues) Range("B2") = 0 End If End If End Sub