Copy and paste macro help

[abinayan]

Hi guys,

I have data sorted into 2000 rows and 10 columns.

Now I have to copy and paste row 5 cells in row 7 to row 8, row 10 to 11, row 13 to 14 and so on in an interval of three for the rest of the 2000 rows. Can anyone help me with a macro that can do this?

Thanks you very much.

 

[jbeaucaire]

Try this:

Option Explicit

Sub CopyPasteRow5()
Dim LR As Long
Dim Rw As Long

    LR = Range("A" & Rows.Count).End(xlUp).Row

    For Rw = 7 To LR Step 3
        Rows(5).Copy Range("A" & Rw).Resize(2).EntireRow
    Next Rw

End Sub

 

[charleymax]

Not sure what you mean here.... Why would you need a macro.

wouldnt you just create a reference here.... i.e C3 is =B2, C4 is =B3 and then copy down the formula...?

 

[abinayan]

The problem is I have sensitive data stored in the other three rows which I need to skip without touching, therefore the complication.

 

[jbeaucaire]

Did you try the macro from post #2?

 

[abinayan]

Try this:

Option Explicit
 
Sub CopyPasteRow5()
Dim LR As Long
Dim Rw As Long
 
    LR = Range("A" & Rows.Count).End(xlUp).Row
 
    For Rw = 7 To LR Step 3
        Rows(5).Copy Range("A" & Rw).Resize(2).EntireRow
    Next Rw
 
End Sub

Hi there,

Thank you very much for your reply. When I ran the code the lines just went blank. I only want to copy and paste 5 cells from each row starting from row 7, column G (G7:K7) to (G8:K8) and copy and paste (G10:K10) to (G11:k11) and so on with an interval of 3 rows.

I hope this makes sense.
Cheers.

 

[jbeaucaire]

I misread your entire question, try this:

Option Explicit

Sub CopyPasteRow5()
Dim LR As Long
Dim Rw As Long

    LR = Range("A" & Rows.Count).End(xlUp).Row

    For Rw = 7 To LR Step 3
        Range("G" & Rw, "K" & Rw).Copy Range("G" & Rw + 1)
    Next Rw

End Sub

 

[abinayan]

I misread your entire question, try this:

Option Explicit
 
Sub CopyPasteRow5()
Dim LR As Long
Dim Rw As Long
 
    LR = Range("A" & Rows.Count).End(xlUp).Row
 
    For Rw = 7 To LR Step 3
        Range("G" & Rw, "K" & Rw).Copy Range("G" & Rw + 1)
    Next Rw
 
End Sub

Hi JB,

your code worked perfectly fine.. I just made two small changes to make it work for me.
I changed the "LR = Range("A" & Rows.Count).End(xlUp).Row" to "LR = Range("G" & Rows.Count).End(xlUp).Row" to start looking from column G.
and I changed copy to cut since the copied formula updated to a new location which I didnt want.

Thanks very much for your help. Really appreciate it!

cheers