Try this macro
Sub search_copy()
'The search criteria should be in column C
'Brooklyn are located in columns c
Sheets("Sheet1").Select
Range("c1").Select
rowcount = Cells(Cells.Rows.Count, "c").End(xlUp).Row
Range("c" & rowcount + 1).Select
ActiveCell.ClearContents
rangerow = rowcount - 1
ActiveCell.FormulaR1C1 = "=countif(r[-" & rangerow & "]c:r[-1],""*Brooklyn*"")"
compte = ActiveCell.Value
ActiveCell.ClearContents
Range("a1").Select
For i = 1 To compte
Sheets("Sheet1").Select
Range("a1").Select
'delete summary and the other columns
Cells.Find(What:="*Brooklyn*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.EntireRow.Cut
'ActiveCell.EntireRow.ClearContents
Sheets("Sheet2").Select
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("a" & rowcount + 1)
Next
Call sort_sheet1
End Sub
Sub sort_sheet1()
Sheets("Sheet1").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Bookmarks