RINCONPAUL
07-05-2016, 04:02 PM
With reference to attached, I have a range (A2:G2) on sheet "Copy", that is continually refreshing every second by 3rd party software. A loop macro copies and pastes that range to sheet "Paste" every second in next blank row of Excel Table. However, there are often periods where the Vol variable (col G) doesn't change, so the information is repeated.
My macro attempts to only copy&paste if the current cell G2 on sheet "Copy" is different from the last paste value in col G in the table on sheet "Paste", but isn't working. Where have I gone wrong?
Dim TimeToRun
Sub StartTimer()
Call ScheduleCopy_Race
End Sub
Sub Copy_Race()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Copy")
Set pasteSheet = Worksheets("Paste")
If pasteSheet.Range("G3").End(xlDown) <> copySheet.Range("G3") Then
copySheet.Range("A2:G2").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
StartTimer
End If
End Sub
Sub ScheduleCopy_Race()
TimeToRun = Now + TimeValue("00:00:02")
Application.OnTime TimeToRun, "Copy_Race"
End Sub
Sub StopTimer()
Application.OnTime TimeToRun, "Copy_Race", , False
End Sub
My macro attempts to only copy&paste if the current cell G2 on sheet "Copy" is different from the last paste value in col G in the table on sheet "Paste", but isn't working. Where have I gone wrong?
Dim TimeToRun
Sub StartTimer()
Call ScheduleCopy_Race
End Sub
Sub Copy_Race()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Copy")
Set pasteSheet = Worksheets("Paste")
If pasteSheet.Range("G3").End(xlDown) <> copySheet.Range("G3") Then
copySheet.Range("A2:G2").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
StartTimer
End If
End Sub
Sub ScheduleCopy_Race()
TimeToRun = Now + TimeValue("00:00:02")
Application.OnTime TimeToRun, "Copy_Race"
End Sub
Sub StopTimer()
Application.OnTime TimeToRun, "Copy_Race", , False
End Sub