Thread: Running Macro Overrides Manual Commands

I have the following macro that copies and pastes live stock market data all day on my computer. If I am working in another workbook while this macro is running, then it is very difficult to copy and paste anything because the macro overrides my manual commands. Is there a way to modify the code to avoid this?

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

I inserted the code and the following part is resulting in a syntax error.

With Workbooks.("Historical Data Run (Thomson)_Live_Vertical").sheets("Sheet1").cells(1,1).currentregion
    Workbooks.("Historical Data Run (Thomson)_Live_Vertical").sheets("Sheet1").cells(83,14).resize(.rows.count,.columns.count)=.Value

I would need to run it on a different computer correct? The macro effects my work in MS Word, Explorer ect..

chuckchuckit- Thanks for your input. I will have to take a closer look at my code and try to re write it with your suggestions. As you can see my code is not very long so I will try to change it to this format Cells.Item(1, 2) = Cells.Item(2, 2). I am not a vba expert so this may take me some time but thanks for your help!

rhudgins - I did rewrite certain sections of my code that were the most vunderable to being corrupted because they were using the "copy" command. And here is link to discussions by people on this forum who helped me to learn how to copy "without using the clipboard".

http://www.excelforum.com/excel-prog...same-time.html

It goes into many different ways to do the data transfer between cells and ranges without using the clipboard (not using the "copy" command).

Here are some examples they gave to me without clipboard use:

Worksheets("Sheet2").Range("G5:H7") = Worksheets("Sheet1").Range("A10:B12").Value

This next one uses row and column cells format:

Worksheets("Sheet2").Cells(3, 5).Resize(1, 4).Value = Worksheets("Sheet1").Cells(1, 5).Resize(1, 4).Value

And this next one they gave me was likely the most powerful use one. But I never fully tested it as I ran out of time to learn it. But I think it resizes the destination too per the source size. If so then this one likely the most efficient:

With Worksheets("Sheet2")
    .Cells(3, 5).Resize(1, 4).Value = Worksheets("Sheet1").Cells(1, 5).Resize(1, 4).Value
End With

I've only been coding about 6 months (used to program in C about 15 years ago), so am new again. Wish I had found this forum months ago, but this is the book I was able to find everything in:

"Excel 2007 VBA Programmer's Reference" 4 authors last names are Green, Bullen, Bovey, Alexander.

Programming is a blast. (That's what I keep telling my wife...).

Have fun!

Chuck

snb – Thanks very much for your examples. That is very helpful as I tested it a bit more and the “with” approach works extremely well. I think I shall adopt it as my standard copying way now without using the clipboard during copying.

Using your “Range” example, “Range” can also be applied to destination ("E3") such as:

With Worksheets("Sheet1").Range("A1:C5")
    Worksheets("Sheet2").Range("E3").Resize(.Rows.Count, .Columns.Count) = .Value
End With

Extremely easy to use (now that I fully understand how it is used). All a person has to do there is change the Range locations to use this code to copy any range to a different worksheet. Top line Range is source, and 2nd line Range is destination, and destination only needs just the upper left location for its range. As the rest of the code does the resizing of destination automatically. Very efficient code it seems.

All 3 examples above can be modified the same way.

Thanks much.

Chuck

I tested these 3 versions of copy and paste "without using the clipboard" and they have the same result.

    'Ranges only
With Worksheets("Sheet1").Range("A1:C5")
    Worksheets("Sheet2").Range("E3").Resize(.Rows.Count, .Columns.Count) = .Value
End With

    'Cells only, Row and Column (R, C,)
With Worksheets("Sheet1").Cells(1, 1).Resize(5, 3)
    Worksheets("Sheet2").Cells(3, 5).Resize(.Rows.Count, .Columns.Count) = .Value
End With
    
    'Ranges and Cells combo
With Worksheets("Sheet1").Range("A1:C5")
    Worksheets("Sheet2").Cells(3, 5).Resize(.Rows.Count, .Columns.Count) = .Value
End With

They all work well!