Macro to copy specific items in a column

[Howardc1001]

where there is text "Sale" in Col b on sheet1, I want to copy the items from Col A : Col D and paste on sheet


I have tried to write code to do this, it cannot get a run time error "Method of range of object_Global failed"


this part is highlighted when getting the run time error

 Range(Cells(i, 1).Cells(i, 4)).Copy

See full code below

 Sub mySales()

Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 2) Like "*Sale*" Then
 Range(Cells(i, 1).Cells(i, 4)).Copy
    Sheets("Vehicle Sales Value").Range("A1").PasteSpecial Paste:=xlPasteValues
    End If
    Next i
    
    
End Sub

[JLGWhiz]

Change the period to a comma before the second Cells.

Range(Cells(i, 1), Cells(i, 4)).Copy

[Howardc1001]

Thanks for the help. Only one item is being copied. I have highlighted 3 items in yellow containing sale in the text that is to be copied

 Sub mySales()

Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 2) Like "*Sale*" Then
 Range(Cells(i, 1), Cells(i, 4)).Copy
    Sheets("Sales Value").Range("A1").PasteSpecial Paste:=xlPasteValues
    End If
    Next i
    
    
End Sub

kindly test & amend

[jolivanes]

Maybe try something like this. Not tested.

Dim sh1 As Worksheet, sh2 As Worksheet, i As Long
Set sh1 = Worksheets("Sale")
Set sh2 = Worksheets("Sales Value")
For i = 2 To sh1.Cells(Rows.Count, 2).End(xlUp).Row
If sh1.Cells(i, 2) Like "*Sale*" Then
sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4).Value = sh1.Cells(i, 1).Resize(, 4).Value
End If
Next i

[Howardc1001]

Thanks Jolivanes

Code works 100%

[jolivanes]

Thanks for letting us know that all is well in SA.
Good luck