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