Try this friend....
I know you can use advancedfilter too, but this is a quick and dirty
one...

Sub CopyRowsBasedOnCellContents()
Dim SourceTab As String
Dim DestTab As String
Dim DestRow As Integer
Dim SearchText As String

'TURN SCREEN UPDATED ON TO KEEP YOU
'FROM SWOLLOWING YOUR TOUNG IF YOU
'HAVE A BUNCH OF ROWS.
Application.ScreenUpdating = False


SourceTab = "Sheet1"
DestTab = "Sheet2"
SearchText = "Tool"

For x = 1 To 99 'YOU'LL NEED TO EDIT THIS TO ACCOUNT FOR THE ROWS
YOUR READING

If InStr(UCase(Sheets(SourceTab).Cells(x, "B")),
UCase(SearchText)) < 0 And Trim(Sheets(SourceTab).Cells(x, "B")) < ""
Then
Sheets(SourceTab).Rows(x & ":" & x).Copy
Sheets(DestTab).Select
Sheets(DestTab).Range("B1").Select

If Trim(Sheets(DestTab).Range("B1").Value) < "" Then
DestRow = Sheets(DestTab).Range("B1").End(xlDown).Row +
1
Sheets(DestTab).Rows(DestRow & ":" & DestRow).Select
End If

ActiveSheet.Paste
Sheets(SourceTab).Select

End If

Next x

Application.ScreenUpdating = True
End Sub

Regards,
Jamie





TonyG001 wrote:
I need a macro that will search for all occurances of the word "tool" in
col B of a spreadsheet and then copy the entire row of each occurance
and paste the rows in another worksheet within the same spreadsheet