Try the below...Edit the sheetnames

Sub CopyRowstoDiffSheet()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngTargetRow As Long

Set ws1 = Sheets("SourceSheetName")
Set ws2 = Sheets("TargetSheetName")

For lngRow = ws1.Cells(Rows.Count, "L").End(xlUp).Row To 4 Step -1
If ws1.Range("L" & lngRow) = "Lost Case" Then
lngTargetRow = WorksheetFunction.Max(4, _
ws2.Cells(Rows.Count, "L").End(xlUp).Row + 1)
ws1.Range("C" & lngRow & ":T" & lngRow).Copy _
ws2.Range("C" & lngTargetRow)
ws1.Rows(lngRow).Delete
End If
Next

End Sub


--
Jacob


"ck13" wrote:

Hi,

I have this database from column C to T and row 4 onwards. What I hope to
achieve is to copy the entire row from C to T to another sheet (similarly
from Column C to T and row 4 onwards) if the cell for that row in Column L is
"Lost Case". At the same time, the selected rows to be copied in the original
sheet should be deleted after being copied.

Another consideration is that new data will be added to the original sheet
from time to time and thus the database might expand or shrink (after being
copied and delete, and the addition or new data). Thus, when the macro is run
and if new 'Lost Case' is found, the new rows should be added to the 2nd
spreadsheet rather than replace old data in spreadsheet 2. Thus it is an
expanding list.

I hope that I am clear with it.

I have thought of using filter, copy and paste but feel that with a macro,
it will be less work for all the users. Any comments on this? Filter or macro?