How to copy multiple rows using If

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
72
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


Code:
Sub Copy_To_Email()

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

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,065
maybe you want this?

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
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:

Code:
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

Board Regular
Joined
Oct 20, 2019
Messages
72
Yes I will be pasting it into another worksheet called Email (Sheet 4). From cell A2.
 

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
72
Didn't you mention where you are going to paste the row?


Try this:

Code:
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

Well-known Member
Joined
Oct 4, 2007
Messages
1,065
this work?

Code:
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

Board Regular
Joined
Oct 20, 2019
Messages
72
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Oct 20, 2019
Messages
72
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
With the following approach you will not have the issue: it leaves the dotted moving outline around the last data copied

Code:
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
 

Forum statistics

Threads
1,077,824
Messages
5,336,593
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top