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