Macro for Copy & Paste and move to next record

[willfrederick]

Hello

I am looking to create a macro that would allow me to copy from one cell and paste into another cell then go back to original copied cell and move down one record.

For Example - Copy from R2 and Paste to C2 then
Move back to R2 and move down one row to R3
Copy R3 and paste to C2, etc. etc. Loop



i = 18 ' Start pasting into column R
' Loop until a blank cell is found
Do While Not Selection.Value = 0
With Sheets("Email Master").Cells(i + -16, 18)
' Select each cell in "Email Master", starting on R2
.Select
' Copy the value into "Email Master", starting on C2
Sheets("Email Master").Cells(3, 2).Value = .Value
End With
Loop

Any help would be amazing.
Thanks
Will

 

[Alex Blakenburg]

Hello Will,

I assume you are just practicing. In the above you have both columns starting in row 2 and your text has Column R incrementing the rows and column C staying at row 2.

There are quite a few different ways of coding this so below is just one way and I have tried to stay with the Cells syntax that your are using.
You want to avoid using the Select statement and using copy paste since they are terribly inefficient and on higher volumes will take a long time to run.

Sub LoopCells()

    Dim inRowFirst As Long
    Dim outRowFirst As Long
    Dim cntr As Long
    Dim sh As Worksheet
    
    Set sh = Worksheets("Email Master")
    inRowFirst = 2
    outRowFirst = 2
    cntr = 0
    
    Do While sh.Cells(inRowFirst + cntr, "R").Value <> ""
        
        sh.Cells(outRowFirst + cntr, "3").Value = sh.Cells(inRowFirst + cntr, "18").Value
        
        cntr = cntr + 1
           
    Loop

End Sub

In referencing the column these 2 will all produce the same result:
Using numbers in the column reference is easier when you want to use a counter to access the column.
sh.cells(RowNo,18)
sh.cells(RowNo,"R")
sh.Range("R" & RowNo)

 

[willfrederick]

Thank you. Though when I use this code, I get a run time error 1004, error application defined. sh.Cells(outRowFirst + cntr, "3").Value = sh.Cells(inRowFirst + cntr, "18").Value

Any thoughts on fix?

 

[Fluff]

Remove the quotes from the numbers on that line.

 

[Alex Blakenburg]

Remove the quotes from the numbers on that line.

Its always that last minute quick manual change that gets you. Thanks for responding it would have been 4 am here.