Copy Paste VBA

[timjohnny]

hi all, I hope you're all well :

I'm trying my luck with writing a Macro today for the first time and would be super grateful for a little help. I'm trying to copy three cells from one row over into another sheet, across three columns. Then I'd like to move on to the next row and copy over across the adjacent three columns. This is what I've written so far:

Sub first_macro()
Worksheets("Rewards Structure").Range("A2:C2").Copy Worksheets("Index").Range("E4:g4")
Worksheets("Rewards Structure").Range("A3:C3").Copy Worksheets("Index").Range("h4:j4")
Worksheets("Rewards Structure").Range("A4:C4").Copy Worksheets("Index").Range("k4:m4")
Worksheets("Rewards Structure").Range("A5:C5").Copy Worksheets("Index").Range("n44")
Worksheets("Rewards Structure").Range("A6:C6").Copy Worksheets("Index").Range("q4:s4")
End Sub

Now, I want to do this for 150 rows, so I could just write another 145 lines of code. But I wondered whether anyone knows a way to condense it?

Thanks a lot for any help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub timjohnny()
   Dim i As Long
   
   With Sheets("Rewards Structure")
      For i = 2 To 151
         .Range("A" & i).Resize(, 3).Copy Sheets("Index").Cells(4, i * 3 - 1)
      Next i
   End With
End Sub

 

[timjohnny]

oh you're the best thank you so so much! This is v exciting I'm starting a VBA course online this week so I'm v excited to become more active here Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.