Copying row if it includes a certain word?

[SpeedyKevin]

Hello all!

Trying to use this macro (listed below) to copy rows (that include a certain word) to another sheet. As it is, it will only copy over the specified range of rows if the cell has "video" but id like it to copy it if it has "video" + any other comments that may be in the cell. Is that possible?






Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowr As Long
Sheets("Task List Template").Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Lastrowr = Sheets("Task List Template2").Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, "D").Value = "video" Then
Cells(i, 1).Resize(, 8).Copy Sheets("Task List Template2").Cells(5, 2)
Lastrowr = Lastrowr + 1
End If
Next
Application.ScreenUpdating = True

 

[JoeMo]

Try replacing this:

= "video"

with this:

Like "*video*"

 

[SpeedyKevin]

Try replacing this:

= "video"

with this:

Like "*video*"

Alright I think my macro is screwed up somewhere in this area. Its only copying one row with video in it.

The rows I'd like the search to start from is Row 7, columns A-H

Sheets("Task List Template").Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Lastrowr = Sheets("sheet3").Cells(Rows.Count, "C").End(xlUp).Row
For i = 7 To Lastrow

 

[DanteAmor]

make these changes

Sub test_copy()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Dim Lastrowr As Long
    Sheets("Task List Template").Activate
    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    Lastrowr = Sheets("Task List Template2").Cells(Rows.Count, [COLOR=#ff0000]"D"[/COLOR]).End(xlUp).Row
    For i = [COLOR=#ff0000]7[/COLOR] To Lastrow
        If [COLOR=#ff0000]LCase[/COLOR](Cells(i, "D").Value) Like[COLOR=#ff0000] "*" & "video" & "*"[/COLOR] Then
            Cells(i, 1).Resize(, 8).Copy Sheets("Task List Template2").Cells([COLOR=#ff0000]Lastrowr[/COLOR], 2)
            Lastrowr = Lastrowr + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub