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

 

[rpaulson]

Application.CutCopyMode = False

 

[Diving_Dan]

If I also didn't want a row to be copied if B5 or the cells below held "RESERVE LIST" do I need to amend the 5th line of code or do I need a new line of code? I've tried playing around with it but I don't really know what i'm doing.

Thanks

With the following approach you will not have the issue: it leaves the dotted moving outline around the last data copied

Sub Copy_To_Email()
  Dim i As Long
  Sheets("Email").Range("A2:D" & Rows.Count).ClearContents
  For i = 5 To Range("B" & Rows.Count).End(xlUp).Row
    If Not Range("B" & i) = "" Then
      Range("E" & i & ":H" & i & ",N" & i).Copy Sheets("Email").Range("A" & Rows.Count).End(xlUp)(2)
    End If
  Next
End Sub

 

[rpaulson]

If Not Range("B" & i) = "" or Range("B" & i) = "RESERVE LIST" Then

 

[Diving_Dan]

If Not Range("B" & i) = "" or Range("B" & i) = "RESERVE LIST" Then

I thought it might have been that so tried it before but if reserve list was in that first cell then it still copied the others??? Any other suggestions?

 

[rpaulson]

Im confused, what do you want the macro to do it it finds "RESERVE LIST" in a cell in Column B??

 

[Diving_Dan]

If Column B is either blank OR has RESERVE LIST I don't want that row of cells to be copied. If Column B has anything else in it then i want the other cells to be copied.

I hope that is clearer, I think i'm getting myself confused too.

 

[rpaulson]

If Not Range("B" & i) = "" or Range("B" & i) <> "RESERVE LIST" Then

 

[Diving_Dan]

I've just tried that and it still pulls that row through to the other sheet.

 

[DanteAmor]

If Column B is either blank OR has RESERVE LIST I don't want that row of cells to be copied. If Column B has anything else in it then i want the other cells to be copied.

I hope that is clearer, I think i'm getting myself confused too.

Try this

Sub Copy_To_Email()
  Dim i As Long
  Sheets("Email").Range("A2:D" & Rows.Count).ClearContents
  For i = 5 To Range("B" & Rows.Count).End(xlUp).Row
    If Range("B" & i) <> "" And UCase(Range("B" & i)) <> UCase("RESERVE LIST") Then
      Range("E" & i & ":H" & i & ",N" & i).Copy Sheets("Email").Range("A" & Rows.Count).End(xlUp)(2)
    End If
  Next
End Sub

 

[rpaulson]

If Range("B" & i) <> "" And UCase(Range("B" & i)) <> "RESERVE LIST" Then