dynamic value not copying correctly

[GrahamR]

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

 

[JW]

You have the range specifically defined as D664, so the loop would
paste the same info in every column because the same info is being
copied every time. Don't understand why you are waiting a second
between iterations either. What exactly are you wanting this code to
accomplish? The code below will do exactly what youtr current code
does without having to do any selections and without any wait times.
Sub thishereh()
For col = 1 To 20
Sheets("Sheet1").Range("D6:d64").Copy _
Destination:=Sheets("Sheet2") _
.Range("B6").Offset(0, col)
Next col
End Sub

 

[GrahamR]

Thanks for that, but maybe I haven't explained well enough. The data
contained in the range D664 is constantly being updated by an external feed
so the same info is not being copied each time. I want to capture that data
every second and paste it into sheet 2, incrementing the column for each
second. The data being pasted into sheet 2 is only from the very first copy
in the loop. Hope this clarifies the problem.
Thanks in anticipation.

 

[JW]

Have you tried incorporating a DoEvents line in your code before the
For loop. That may work for what you need. I would still recommend
using the For loop that I posted so that you don't have all of those
selects in there.

 

[Tom Ogilvy]

Try something like this:

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
doevents
Application.Calculate
doevents
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

 

[JE McGimpsey]

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("D664")
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), _
procedure:="RecordData"
Else
nIteration = 0
End If
End Sub