What's causing the source sheet to change? Using Wait "suspends all
Microsoft Excel activity" (XL/VBA Help "Wait Method"), so the cells may
not be updating.

Perhaps:

Public Sub RecordData()
Const cnMax As Long = 20
Static rSource As Range
Static rDest As Range
Static nIteration As Long
If nIteration = 0 Then
nIteration = 1
Set rSource = Sheets("Sheet1").Range("D6:D64")
Set rDest = Sheets("Sheet2").Range("B6").Resize( _
rSource.Rows.Count, 1)
End If
rDest.Offset(0, nIteration - 1).Value = rSource.Value
nIteration = nIteration + 1
If nIteration <= cnMax Then
Application.OnTime _
EarliestTime:=Now + TimeSerial(0, 0, 1), _
procedu="RecordData"
Else
nIteration = 0
End If
End Sub

In article ,
GrahamR wrote:

I am trying to copy a range of cells from sheet 1 to sheet 2 every second,
but incrementing the sheet 2 column every loop to create a history trail. The
code is working but at run time it pastes only the first copy of the loop for
every iteration. So all the pasted columns are exactly the same. In debug the
code seems to work OK. One solution may be to clear the clipboard within the
loop. Any ideas anyone please.




Dim col As Integer

For col = 1 To 20

'DO EVERY SECOND
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Sheets("Sheet1").Select
Range("D6:d64").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B6").Select
ActiveCell.Offset(0, col).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False


Next col



End Sub