Anyone have an idea if this is possible

--
Drrott
-----------------------------------------------------------------------
Drrott1's Profile: http://www.excelforum.com/member.php...fo&userid=1546
View this thread: http://www.excelforum.com/showthread.php?threadid=27048

Drrott1


set a variable with the row number that has the 1st occurance of yo
find text
eg rStart = 12

find last row that matches text

rLast = 20



use this code to copy & paste rows

Rows(rstart & ":" & rlast).Copy

rows(30).paste
or
sheets("sheet2").rows(1).past

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=27048

Drrott1 wrote in message ...
Anyone have an idea if this is possible?

Heres a piece of code that may help a bit - its by no means complete
- and im sure this could be done better

in particular it needs improved in two parts...

1. can anyone inform how to select the whole row of a current cell
the sub only copuies the cell

2. Im sure you can get cell.Address(....) to pass out the full address
i.e. including the worksheet - then you wont have to flick between
worksheets
as you loop through colFirstCellInRowAddress at the tail of the sub


Public Sub InputRoute()
Dim Route As Variant

Dim colFirstCellInRowAddress As New Collection

Columns("A:A").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

Route = InputBox("Please Enter route", "Route Selection")

For Each cell In Selection
If cell.Value = Route Then
colFirstCellInRowAddress.Add (cell.Address)
End If
Next cell

For Each obj In colFirstCellInRowAddress

ActiveSheet.Range(obj).Rows.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Range(obj).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Next obj

End Sub