Macro for Copy & Paste and move to next record

willfrederick

New Member
Joined
Mar 4, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
626
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Mar 4, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Remove the quotes from the numbers on that line.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top