Search for a value in a column and copy row to new sheet for all matching values in new sheet

[nickusa99]

I found this on http://www.techonthenet.com/excel/macros/search_for_string.php and it's working perfect, I am wondering if someone can help and let me know how to run this on all sheets in workbook.

I have several sheets in workbook for each country and I want to combine all search column into new one.

here is the macro :-
Sub SearchForString()


Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute



'Start search in row 7
LSearchRow = 7

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column B = "Laptops", copy entire row to Sheet2
If Range("B" & CStr(LSearchRow)).Value = "Laptops" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("CANADA").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A2
Application.CutCopyMode = False
Range("A2").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

 

[jmahmoud]

This code worked well for me, however if I want to search something else, it replaces what it already found with my new search. I want to be able to search multiple things and have them pasted on one spreadsheet.