Your description isn't very clear. The code below "may" work, but if not use
it as a model or be a little clearer what you are looking for.

Sub findford()
Set Lookuprange = Sheets("Sheet1").Range("a2:a1040")

Sh2RowCount = 1
For Each cell In Lookuprange
If cell = "ford" Then
With Sheets("Sheet1")
.Range(.Cells(cell.Row, "A"), _
.Cells(cell.Row, "AC")).Copy _
Destination:= _
Sheets("Sheet2").Cells(Sh2RowCount, "A")
Sh2RowCount = Sh2RowCount + 1
End With
End If
Next cell


End Sub


"lee willis" wrote:

I need to know how to do a vlookup and then pull all the data under it in the
field that is the same as the lookup. ie i am looking up ford and in the
lookup range there is more than 1 ford in a row? so lookup range is
sheet1a2:ac1040. ford is in cells a407 thur a597. i need to pull a407 thur
ac407 in row 1 of sheet2 i know the formula for that what i am having a hard
time with is row2 of sheet 2 needs to be filled with a408 thur ac408. can
some one help me with this?