help needed

[gavgee]

a basic example of my spreadsheet ist here

date name status

12.03.05 bob renewed
12.04.05 fred waiting
12.12.05 john lapsed

I need a macro that cuts out all the renewed and lapsed rows of
data and automatically send them to another worksheet.

can anyone help with this
I'd love it if u could

gav

[hideki]

This may not the best solution. Please try it if its suit you. If you have any comment how to make it better please advise me. I'm learning myself.

Best regards,

Sub RenewedNLapsed()

Dim strValue As String 'renewed or lapsed
Dim lngLastRow As Long 'last row
Dim lngRow As Long 'current row
Dim lngCopyRow As Long 'row to copy to
Dim wsNewsheet As Worksheet 'sheet to copy to

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set wsNewsheet = Sheets("Sheet2")

lngCopyRow = 1
'since this will delete the row, start from the last line to top
For lngRow = lngLastRow To 2 Step -1
'cell C value, change to lower case to avoid case sensitif
strValue = LCase(Cells(lngRow, "C").Value)
'check if the value renewed or lapsed
If strValue = "renewed" Or strValue = "lapsed" Then
'copy the row from column A to column C
Range(Cells(lngRow, "A"), Cells(lngRow, "C")).Copy wsNewsheet.Cells(lngCopyRow, "A")
'delete the row after copy
Rows(lngRow).Delete
'increase row no for the copy to sheet
lngCopyRow = lngCopyRow + 1
End If
Next

End Sub