You could try something like:

Sub FindCity()
LookFor = "Redwood City"
Set OuputRange = Range("E1")
r = 0
For Each c In Range("A1:A100")
a = InStr(c, LookFor)
If a > 0 Then
OuputRange.Offset(r, 0) = c
r = r + 1
End If
Next
End Sub

Why don't you start off by recording a macro of you doing it one time. Then
you can add a loop to it.

Here is an example where I was copying a total value out of several sheets
and putting  the value into the one, :

Sub MoveTotal()
Dim strCounter As Integer

For counter = 1 To 230
strCounter = counter
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Range("A1:J1").Select
Selection.Copy
Sheets("SummaryLessMCA").Select
Range("A1").Select
ActiveCell.Offset(strCounter, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Year 2001 less McA Salaried").Select
Next counter

End Sub