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]

maybe you want this?

 If Range("B5") <> "" Then Range("E5:H5,N5").Copy

hth,
Ross

 

[DanteAmor]

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. That is, if the cell contains something then copies the row?

I need to replicate this for the 19 rows beneath this. Each row must be verified, if so, then you can cycle to verify each cell.

Didn't you mention where you are going to paste the row?


Try this:

Sub Copy_To_Email()
  Dim i As Long
  For i = 5 To 24
    If Not Range("B" & i) = "" Then
      Range("E" & i & ":H" & i & ",N" & i).Copy
      'paste somewhere
    End If
  Next
End Sub

 

[Diving_Dan]

Yes I will be pasting it into another worksheet called Email (Sheet 4). From cell A2.

 

[Diving_Dan]

Didn't you mention where you are going to paste the row?


Try this:

Sub Copy_To_Email()
  Dim i As Long
  For i = 5 To 24
    If Not Range("B" & i) = "" Then
      Range("E" & i & ":H" & i & ",N" & i).Copy
      'paste somewhere
    End If
  Next
End Sub

I have just tried your code and it only takes the last line row of data, not several rows. Would i have to write the code to look at each row individually.

Thanks

 

[rpaulson]

this work?

Sub Copy_To_Email()

  Dim i As Long
  r = 2
  For i = 5 To Cells(Rows.Count, "B").End(xlUp).Row
    If Not Range("B" & i) = "" Then
      Range("E" & i & ":H" & i & ",N" & i).Copy
      Sheets("Email").Range("A" & r).PasteSpecial Paste:=xlPasteValues
      r = r + 1
    End If
  Next
End Sub

-Ross

 

[Diving_Dan]

Thanks Ross that worked a treat. The only thing bugging me now is that once the data is pasted onto my second worksheet I still have the moving dotted line showing what the last data to be copied would be. Can I stop that?

Thanks

Dan

 

[DanteAmor]

I have just tried your code and it only takes the last line row of data, not several rows. Would i have to write the code to look at each row individually.

Thanks

The code is to go through column B from B5 to B24 (20 rows)
If you explain clearly where you want to check, I can adjust the code.
You must also specify where exactly you want to paste, that is, in the "Email" sheet, but in which column and in which row, perhaps, after the last row with data from a certain column.

 

[Diving_Dan]

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

 

[DanteAmor]

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