Can someone help me re-write this code without using the clipboard? I have the following macro that copies and pastes data every 10 seconds all day long and as a result I cannot cleanly use the copy and paste function on my computer for any other programs.
Option Explicit
Dim icount As Integer, Inumber As Integer, rStart As Range
Sub CopyLiveTradeData()
Application.OnTime TimeValue("12:05:00"), "StartOnTime"
End Sub
Private Sub StartOnTime()
icount = 0
Inumber = 20000
Workbooks("Historical Data Run (Thomson)_Live_Vertical").Worksheets("Sheet1").Select
Call OnTimeMacro
End Sub
Private Sub OnTimeMacro()
If icount <= Inumber Then
icount = icount + 1
Application.OnTime Now + TimeValue("00:00:10"), "RunEveryXMinute"
Else
Workbooks("Historical Data Run (Thomson)_Live_Vertical").Worksheets("Sheet1").Select
End If
End Sub
Private Sub RunEveryXMinute()
Dim rDate As Range
Dim dt As Date
Dim rCopyFrom As Range
Dim rCopyTo As Range
Dim rTrade As Range
Dim sh As Worksheet
Dim iColumnsToSkip As Integer
iColumnsToSkip = 25
Set sh = Workbooks("Historical Data Run (Thomson)_Live_Vertical").Worksheets("Sheet1")
With sh
Set rDate = .Range("R1")
dt = rDate.Value
Set rCopyFrom = .Range("O59").Resize(1, 2)
Set rTrade = .Range("N82")
On Error GoTo EF
Application.EnableEvents = False
Application.ScreenUpdating = False
Do While rCopyFrom.Cells(1, 1).Value <> ""
Set rCopyTo = rTrade
If rCopyTo.Value <> "" Then
If rCopyTo.Offset(1, 0).Value <> "" Then
Set rCopyTo = rCopyTo.End(xlDown)
End If
Set rCopyTo = rCopyTo.Offset(1, 0)
End If
Set rCopyTo = rCopyTo.Resize(1, 3)
With rCopyTo.Cells(1, 1)
rDate.Copy 'for format
.Value = dt
.PasteSpecial Paste:=xlPasteFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
rCopyFrom.Cells(1, 1).Copy
rCopyTo.Cells(1, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
rCopyFrom.Cells(1, 2).Copy
rCopyTo.Cells(1, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
With rCopyTo.Font
.ThemeColor = xlThemeColorDark1
.ColorIndex = 2
.TintAndShade = 0
End With
Set rCopyFrom = rCopyFrom.Offset(0, iColumnsToSkip)
Set rTrade = rTrade.Offset(0, iColumnsToSkip)
Loop
End With
Call OnTimeMacro
EF:
Application.EnableEvents = True
End Sub
Bookmarks