Offset Copy & Paste

[ILoveCheese]

I am trying to learn how to use loops in regards to copying and pasting data. If I have a range of data, C10:C15, how do get that to paste to D10:D15? I don't want to copy the whole range all at once. I would like to learn how to offset the data instead. I tried putting some code together from what I've seen on the board, but its not getting me anywhere.

Dim M As Integer
For M = 1 To 5
Range("C10").Copy Destination:=Range("D10")
ActiveCell.Offset(1, 0).Select
Next M
End Sub

I don't know how to finish this so the paste destination offsets like the copy location.

 

[Jonmo1]

Try like this, without the select (that just makes it slower)

Dim M As Integer
For M = 10 To 15
Range("C" & M).Copy Destination:=Range("D" & M)
Next M
End Sub

Hope this helps..

 

[ILoveCheese]

It works, and it gives me something new to experiment with. Thank you.

 

[Jon von der Heyden]

Hi

Maybe:

Dim i As Integer

For i = 10 To 15
    With Range("C" & i)
        .Copy
        .Offset(0, 1).PasteSpecial xlPasteAll
    End With
Next i

I have chosen to use 10 to 15 instead, which is the equivalent row number that is being copied.

Also note code indentation which makes it easier to follow.

HTH
Jon

 

[ILoveCheese]

Thanks Jon, I always like learning different approaches.

 

[ILoveCheese]

I tried adding another column of data along with another integer.

Dim M As Integer
Dim X As Integer
For M = 10 To 15
For X = 10 To 15
Range("C" & M).Copy Destination:=Range("D" & M)
Range("E" & X).Copy Destination:=Range("F" & X)
Next M
Next X
End Sub

This gives me a complie error "Invalid Next control variable reference". What is the appropriate method for using multiple integers?

 

[Jonmo1]

You can use the same variable for both.

Dim M As Integer
For M = 10 To 15
    Range("C" & M).Copy Destination:=Range("D" & M)
    Range("E" & M).Copy Destination:=Range("F" & M)
Next M
End Sub

But the reason yours failed is because you had the Next M and Next X reversed...
The First NEXT, should be whatever was the LAST For...

For M = 10 to 15
    For X = 10 to 15
 
        do stuff
 
    Next X
Next M

 

[ILoveCheese]

What if the range is different? For example, moving E20:E30 to F20:F30?