Help with Copy & Paste Loop Cell by Cell

BRE3

New Member
Joined
Apr 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

I've been working on this macro and can't seem to get it to work.

What I need it to do is this:
1. Start by Copying Cell A2 in Worksheet "CP Report Pull"
1587155240494.png

2. Then paste the copied A2 in to cell K2 (Circled in Green) on Worksheet "Week View" (Also Circled in Green)
3. After pasting in to K2, copy cell K14 (Circled in Red)
1587155479489.png

4. Paste the copied K14 value in to cell A2 (Circled in Green) on Worksheet "Sheet2"(Also Circled in Green)
1587155800875.png

*5. I need this Macro to be able to repeat itself. For example, after it is done doing A2, it will repeat the above process for A3 in Worksheet "CP Report Pull" and know to paste in cell A3 on Worksheet "Sheet2" and so on.*

As of right now this is the macro that executes the initial action for cell A2.
1587156269138.png


Can somebody help me?
 

Attachments

  • 1587155188681.png
    1587155188681.png
    153.4 KB · Views: 5

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
try this:
VBA Code:
Sub Do_it()

Dim rs1, rs2, rs3 As Worksheet
Set rs1 = Worksheets("CP Report Pull")
Set rs2 = Worksheets("Week View")
Set rs3 = Worksheets("Sheet2")


For r = 2 To rs1.Range("A" & Rows.Count).End(xlUp).Row

rs2.[K2] = rs1.Cells(r, "A").Value
rs3.Cells(r, "A") = rs2.[K14].Value

Next r

MsgBox "Done"

End Sub

hth,
-Ross
 
Upvote 0
See if this works:

VBA Code:
With Sheets("CP Report Pull")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lr
        Sheets("Week View").Range("K2").Value = .Range("A" & i).Value
        Sheets("Sheet2").Range("A" & i).Value = Sheets("Week View").Range("K14").Value
    Next
End With
 
Upvote 0
You should also maybe clear column A on Sheet2 at the start of the macro if required
 
Upvote 0
How about

VBA Code:
Sub LoopCells()
  Dim c As Range
  For Each c In Sheets("CP Report Pull").Range("A2", Sheets("CP Report Pull").Range("A" & Rows.Count).End(3))
    Sheets("Week View").Range("K2").Value = c.Value
    Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2).Value = Sheets("Week View").Range("K14").Value
  Next
End Sub
 
Upvote 0
try this:
VBA Code:
Sub Do_it()

Dim rs1, rs2, rs3 As Worksheet
Set rs1 = Worksheets("CP Report Pull")
Set rs2 = Worksheets("Week View")
Set rs3 = Worksheets("Sheet2")


For r = 2 To rs1.Range("A" & Rows.Count).End(xlUp).Row

rs2.[K2] = rs1.Cells(r, "A").Value
rs3.Cells(r, "A") = rs2.[K14].Value

Next r

MsgBox "Done"

End Sub

hth,
-Ross
Can you insert the names of my worksheets and cells into what you sent? I’m new to coding and am not quite sure how to insert it.
Thank you!
 
Upvote 0
Can you insert the names of my worksheets and cells into what you sent? I’m new to coding and am not quite sure how to insert it.
Thank you!
Never mind I didn’t read the whole thing!
 
Upvote 0
try this:
VBA Code:
Sub Do_it()

Dim rs1, rs2, rs3 As Worksheet
Set rs1 = Worksheets("CP Report Pull")
Set rs2 = Worksheets("Week View")
Set rs3 = Worksheets("Sheet2")


For r = 2 To rs1.Range("A" & Rows.Count).End(xlUp).Row

rs2.[K2] = rs1.Cells(r, "A").Value
rs3.Cells(r, "A") = rs2.[K14].Value

Next r

MsgBox "Done"

End Sub

hth,
-Ross
This worked! Thank you Ross!
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top