Copy rows to sheet2 based on a value search in a range and move only selected columns

[bulletshiva]

Hello,

I have a task to move the rows to sheet 2 based on a search value in a range. The below code will search a particular column and copies all the rows to sheet2.
Now i want to check a range of columns for a value and copy only required columns like Item_id and description to sheet2.

request you to modify the code.

Item id	Description	Date	Range
A002M22002	pump	2/8/2018	K	L	Z	X	Q	S
A002M22003	handle	1/8/2018	K	M	Z	L	Q	S
A002M22004	washer	5/8/2018	K	N	Z	X	L	E

<colgroup><col><col span="2"><col span="6"></colgroup><tbody>
</tbody>

Sub Button1_Click()
a = Worksheets("sheet1").Cells(Row.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("sheet1").Cells(i, 3).Value = "L" Then
Worksheets("sheet1").Row(i).Copy
Worksheets("sheet2").Activate
b = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("sheet1").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("sheet1").Cells(1, 1).Select
End Sub

 

[Fluff]

How about

Sub Button1_Click()
Dim a As Long, b As Long, r As Long, c As Long
a = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).row
b = Worksheets("sheet1").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For r = 2 To a
   For c = 4 To b
      If Worksheets("sheet1").Cells(r, c).Value = "L" Then
         Worksheets("sheet1").Range("A" & r).Resize(, 2).Copy Worksheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
         Exit For
      End If
   Next c
Next r
ThisWorkbook.Worksheets("sheet1").Cells(1, 1).Select
End Sub