Hello, I'm trying to copy all rows that meets the condition in column "B" from all sheets in the workbook to a new worksheet.
The name of the new worksheet which is equal to the condition to be copied is defined on "setup" worksheet. Here is the code:

Sub test_copy()
Dim i As Long
Dim lr1 As Long, lr2 As Long
Dim Condition As String
Dim Sheet As Worksheet, wks1 As Worksheet, wks2 As Worksheet
Dim Search_String As Range
Set Search_String = Worksheets("setup").Range("Selected_Product")
Worksheets.Add(After:=Worksheets("setup")).Name = Search_String

Set wks2 = Worksheets(Search_String.Value) 'copy to

On Error Resume Next

For Each Sheet In ThisWorkbook.Worksheets
If Sheet.Name <> wks2.Name Then
    Set wks1 = ActiveSheet 'copy from
    lr1 = wks1.Cells(Rows.Count, "B").End(xlUp).Row
    With Sheet
      For i = 2 To lr1
         Condition = wks1.Cells(i, "B").Value
             If Len(Condition) <> 0 Then
               If Condition = Search_String.Value Then
                  lr2 = wks2.Cells(Rows.Count, "A").End(xlUp).Row + 1
                  wks1.Cells(i, "B").EntireRow.Copy Destination:=wks2.Cells(lr2, "A")
               End If
          End If
      Next i
    End With
End If
Next Sheet
End Sub
unfortunately the code besides creating a new worksheet does nothing, where did I go wrong? Any help will be appreciated.