Drrott1 said:
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
