Picking data from another workbook

[jdcollins21]

I got a long list of data which I sort by column C in Book1.xls.

Example

xxxx yyyy (Column C) zzzz aaaa
aaaa bbbb (Column C) dddd eeee

Column C is one of 12 city names.
There's data in the other four columns but it's inconsequential.

What I want to do is (In a second Workbook)
Have an Inputbox that asks for the city Name.
Next, It would then sort through Sheet1 of Book1.xls for all rows that
contain that city name in column C
Next, Copy the first five columns of the sheet1 in Book1 (to &
including Column E)
Finally, Paste those in the Active workbook in sheet1 starting in Cell
A2

 

[Tom Ogilvy]

Sub copydata()
Dim sCity As String
Dim bk2 As Workbook, bk1 As Workbook
Dim rng1 As Range, rng2 As Range
Dim rw As Long
Dim cell As Range

sCity = InputBox("Enter City Name:")
Set bk2 = Workbooks("Book2.xls")
' or Set bk2 = ActiveWorkbook
Set bk1 = Workbooks("Book1.xls")

With bk1.Worksheets("Sheet1")
Set rng1 = .Range(.Cells(1, 3), _
.Cells(Rows.Count, 3).End(xlUp))
End With
rw = 1
rng2 = bk2.Worksheets("sheet1").Range("A2")
For Each cell In rng1
If LCase(cell.Value) = LCase(sCity) Then
cell.Offset(0, -2).Resize(1, 5) _
.Copy Destination:=rng2(rw)
rw = rw + 1
End If
Next

End Sub

 

[jdcollins21]

Tom,

Thanks for the assist but I have a problem.
I tried running it.
I even set up a book1.xls and book2.xls to run it
but I keep coming up with the same error

"Run-time error '91';
Object variable or With block variable not set"

The problem's at:

With bk1.Worksheets("Sheet1")
Set rng1 = .Range(.Cells(1, 3), _
.Cells(Rows.Count, 3).End(xlUp))
End With
rw = 1
rng2 = bk2.Worksheets("sheet1").Range("A2")
For Each cell In rng1
If LCase(cell.Value) = LCase(sCity) Then
cell.Offset(0, -2).Resize(1, 5) _
.Copy Destination:=rng2(rw)
rw = rw + 1
End If
Next

End Su