How to copy multiple rows using If

[Diving_Dan]

Hi all,

I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need to replicate this for the 19 rows beneath this. Do I need to Keep writing the same code for each row or can I write a code to cover all the rows.

Thanks

Sub Copy_To_Email()

    If Not Range("B5") = "" Then
        Range("E5:H5,N5").Copy
        
    End If
        
End Sub

 

[Diving_Dan]

Perfect that worked. Thank you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[My Aswer Is This]

Hi Dante,

Ross posted some code above which works. The only bug with it is that once pasted it leaves the dotted moving outline around the last data copied.

I basically want the code to look at B5 and if there is anything in that cell to copy Cells E5:H5 & N5 and to paste it into A2 on the Email sheet. Then go down each row down to B24 doing the same thing and pasting it onto the next available row within the email sheet. So anything in B6 then copy E6:H6 & N6 etc.

I hope this makes sense.

Thanks

Dan

To remove those matching ants just add this line of code near the bottom of your script:

Application.CutCopyMode = False