Sub GetData()

'Ideally I don't want it to activate the workbook since I want to be
'working in other applications or other workbooks while it runs

MyTime = Time

If MyTime = TimeValue("6:30AM") And _
MyTime <= TimeValue("4:00PM") Then

With ThisWorkbook
Range("SumDaily").Copy
'Application.CutCopyMode = False
With .Sheets("Sheet3")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
.Range("B" & NewRow).PasteSpecial _
Paste:=xlPasteValues, _
Transpose:=True
.Range("A" & NewRow) = MyTime
End With
End With
End If
Application.OnTime Now + TimeValue("00:01:00"), "GetData"

End Sub





" wrote:

Hi,

On one worksheet (Sheet1) I have a named range ("SumDaily") which is
pulling data from an outside source that updates automatically. I
would like to capture this data once every minute, paste as values
(transposed) on Sheet3 with a time stamp. I want to append it each
time to the bottom of the list so that at the end of a 6.5 hour period
I have 390 rows of data with respective timestamps (Row 1 has headers,
Column A would be time stamp).

Below is a recorded version of how I would do it for one line, but I
don't know how to append it to the bottom of a list, nor do I know how
to make it run automatically every 60 seconds (relative beginner with
VBA). I'm using Excel 2007, Windows XP.

Sub Macro1()

'Ideally I don't want it to activate the workbook since I want to be
working in other applications or other workbooks while it runs

Windows("MyWorkbook.xlsx").Activate
Sheets("Sheet1").Select
Application.Goto Reference:="SumDaily"
Selection.Copy
Sheets("Sheet3").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

A bonus would be to make it run only during market hours (9:30 AM -
4:00 PM), but this isn't necessary since I can just open and close the
workbook at those times.

Many thanks!
Steve