Cut, copy and paste excel

Question

In VBA are you able to carry out a copy and paste straight after a cut and paste. I ask because If statement within a for loop that needs to cut a row of data and then copy the row directly underneath. When I run the code it performs that cut but never the copy. I looked into this online and the only thing I could come up with is clearing the clipboard. Sadly this not help :(.

Here is my code:

im r As Range
Dim i As Long, i2 As Long, i4 As Long
i = 2
i2 = 15
i3 = 70
i4 = 3
For Each r In ActiveSheet.UsedRange
v = r.Value
    If InStr(v, "ION") > 0 And InStr(v, "Dog") > 0 Then
        Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column + 2)).Cut
        Range(Cells(i, 8), Cells(i, 10)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range(Cells(r.Row + 1, r.Column), Cells(r.Row + 1, r.Column + 2)).Copy
        Range(Cells(i4, 8), Cells(i4, 10)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        i = i + 2
        i4 = i4 + 2

There is then more code using elseif's to search for other words but the code above is the code in question.

To reiterate, the cut/paste works. the copy/paste does not.

Thanks.

score 2 · Answer 1 · edited May 23 '17 at 12:21

2

I'm not really able to replicate the problem you're having, but you could try changing these three lines:

Range(Cells(r.Row + 1, r.Column), Cells(r.Row + 1, r.Column + 2)).Copy
Range(Cells(i4, 8), Cells(i4, 10)).Select
ActiveSheet.Paste

To this:

Range(Cells(r.Row + 1, r.Column), Cells(r.Row + 1, r.Column + 2)).Copy _
    Range(Cells(i4, 8), Cells(i4, 10))

You can do the same thing with the .Cut method:, which would become:

Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column + 2)).Cut _
    Range(Cells(i, 8), Cells(i, 10))

NB: It is rarely necessary to activate, Select or otherwise "click" the range objects that you need to work with. Here is a great explanation:

How to avoid using Select in Excel VBA macros

edited May 23 '17 at 12:21

Community

1
1

answered Sep 16 '14 at 16:19

David Zemens

53,033
11
81
130

Hi David, Thank you for you reply. That is a much tidier way of writing the code. The issue still persists tho. It's incredibly annoying =P – Lancealott92 Sep 18 '14 at 10:53
What happens when you step through the code? I am surprised if it fails to copy without raising an error of any sort. – David Zemens Sep 18 '14 at 12:52

Cut, copy and paste excel

1 Answers1