VBA code to move row to a different sheet based on cell value

[lolly150]

Hoping some kind person can help me with some VBA code

I have a worksheet named "caseload" columns A-T
I'd like to use a VBA code to move complete rows to a new worksheet named "finished episodes" when Column A has the word either "Discharged" or "died" (which is the result of a formula in this cell)
I have one Title Row so from row 2. I'd also like to keep the destination data, so preferably not to overwrite data in the destination worksheet but add it to the end.

Please can someone help with this?

Thanks

 

[JEC]

This could be enough. It does overwrite the rows everytime, but shouldn't be a problem since the dataset is increasing?

Sub jec()
  Application.ScreenUpdating = False
  With Sheets("caseload").Cells(1).CurrentRegion
    .AutoFilter 1, Array("died", "Discharged"), 7
    .Offset(1).Resize(, 20).Copy
     Sheets("finished episodes").Range("A2").PasteSpecial xlPasteValues
    .AutoFilter
  End With
End Sub

 

[MARK858]

It does overwrite the rows everytime,

@JEC you could just make the amendment below

Sub jec()
  Application.ScreenUpdating = False
  With Sheets("caseload").Cells(1).CurrentRegion
    .AutoFilter 1, Array("died", "Discharged"), 7
    .Offset(1).Resize(, 20).Copy
     Sheets("finished episodes").Range("A" & Rows.count).End(xlUp)(2).PasteSpecial xlPasteValues
    .AutoFilter
  End With
End Sub

 

[JEC]

@mark, ofcourse! But if you go like this, you create duplicates every time you run this macro.

 

[MARK858]

@mark, ofcourse! But if you go like this, you create duplicates every time you run this macro.

True, but personally I am a bit unsure exactly what the OP is doing i.e. whether all the formulas change each time (in which case then it is a total new set of results each time and so would need appending to the bottom of the previous results each time) or if it is just the last row that is changing which would be fine with the overwrite or even a Worksheet event.

 

[lolly150]

Thank you both for your replies, apologies for late reply.

The second code worked perfectly, is there any way to delete the entry on the Caseload tab once it's been moved?

 

[MARK858]

Try (untested)...

Sub jec3()
  Application.ScreenUpdating = False
  With Sheets("caseload").Cells(1).CurrentRegion
    .AutoFilter 1, Array("died", "Discharged"), 7
    .Offset(1).Resize(, 20).Copy
    Sheets("finished episodes").Range("A" & Rows.count).End(xlUp)(2).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    .Offset(1).EntireRow.Delete
    .AutoFilter
  End With
End Sub

 

[lolly150]

Thanks so much Mark..... worked perfectly!

 

[lolly150]

Hi Mark

If I wanted to include more than one caseload tab would that be possible?
i.e caseload 1, caseload 2, caseload 3.
Would you mind adding this to the code please