Picking data from another workbook

  • Thread starter Thread starter jdcollins21
  • Start date Start date
J

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
 
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
 
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
 
Back
Top