Solved: Look for word then copy and paste row

[Nicolaf]

Hi,

I need to do a search for a word in some Excel rows eg. "hello" in sheet1.
The word will be contained in rows first cell (so in column A)
When I find word hello I need to copy whole row in sheet2.

Thanks!
Nic

:think:

[Bob Phillips]

Off the top


On Error Resume Next
Set cell = Columns(1).Find("hello")
ON Error Goto 0
If Not cell Is Nothing Then

cell.EntireRow.Copy Worksheets("Sheet2").Range("A1")

'etc.
End If

[Nicolaf]

Thanks.
This copies one row only ie. first one from list.
What if I want to copy all rows which contain hello in first cell?
Let's say we have 10 rows starting from A4 to A14.

How do we do that?

Thanks,
Nic :think:

[lucas]

Sheet notices would be your sheet2 and WITHDRAWN would be your hello:

Option Explicit
Sub Main()

Dim i As Long

For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then
ActiveSheet.Rows(i).Copy Sheets("Notices").Cells(TargetRow(Sheets("Notices"), 1), 1)
' ActiveSheet.Rows(i).Delete
End If
Next i

End Sub

Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
'change targetrow to 2 to move the rows starting on the second row
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function

[lucas]

Sorry Nic I missed the part about column A.

Change this line:

If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then


to

If UCase(ActiveSheet.Cells(i, 1).Value) = "WITHDRAWN" Then
for column A

Example attached but be sure to comment out this line if you don't want to remove the rows from the first sheet. It is set up to move the rows not copy them as is:
ActiveSheet.Rows(i).Delete