Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic value not copying correctly
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic value not copying correctly
You have the range specifically defined as D6:D64, 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic value not copying correctly
Thanks for that, but maybe I haven't explained well enough. The data
contained in the range D6:D64 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" wrote: You have the range specifically defined as D6:D64, 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic value not copying correctly
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. GrahamR wrote: Thanks for that, but maybe I haven't explained well enough. The data contained in the range D6:D64 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" wrote: You have the range specifically defined as D6:D64, 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic value not copying correctly
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 -- regards, Tom Ogilvy "GrahamR" wrote: Thanks for that, but maybe I haven't explained well enough. The data contained in the range D6:D64 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" wrote: You have the range specifically defined as D6:D64, 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic value not copying correctly
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bar chart not copying correctly into powerpoint | Excel Discussion (Misc queries) | |||
Copying Formulas - correctly stated | Excel Discussion (Misc queries) | |||
Dynamic chart not updating correctly. | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming |