Picking data from another workbook
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Picking data from another workbook
 

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


--
Regards,
Tom Ogilvy


"jdcollins21" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Picking data from another workbook
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com