Copy Autofiltered values from one sheet to another stopping at one column.

[JasonWilliam]

I presently have a code that copies all rows from one sheet (named BOM) to the next available row on another sheet (named BOM Data). I would like to autofilter the data on the BOM sheet to only copy the rows STOPPING on row that contain the word "Laser". So far this is my code.

Private Sub CommandButton1_Click()

With Range("A4:I" & Range("A" & Rows.Count).End(xlUp).Row)
.AutoFilter field:=9, Criteria1:="Laser"
Sheets("BOM Data").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub

The problem I am having is it might filter the data correctly but it still copies all rows that have data in them even if they do not contain laser in them. Also I would like the autofilter to shut off afterwards so I do not have to it manually.

Thank you.

 

[JEC]

Try:

Sub jec()
 Application.ScreenUpdating = False
 With Range("A4:I" & Range("A" & Rows.Count).End(xlUp).Row)
   .AutoFilter 9, "Laser"
   .Offset(1).Copy
    Sheets("BOM Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   .AutoFilter
 End With
End Sub

 

[JasonWilliam]

That worked! Thank you, I've been trying for about a day to figure this out...lol

 

[JEC]

You’re welcome!