Issues When Using VBA to Automatically Move Row to Another Sheet Based On Cell Value

[EBe]

Hi,

I’m completely new to VBA and have today used this site to find the code to automatically move a row to another sheet in excel based on cell value. Credit to Joe4 for the code left on another thread! However, I’m having a problem where only one row is appearing on the second sheet, and anything that was there before is disappearing. For example, a row of data about Patient 1 is moved from sheet 1 to sheet 2 when data in column I reads ‘discharged’. When a second row, Patient 2, also reads ‘discharged’, it moves from sheet 1 to sheet 2, but the data about Patient 1 disappears. How do I get the data to appear simultaneously in a table for future review?

The code I’m using is:

Private Sub Worksheet_Change(ByVal Target As Range)



' Check to see only one cell updated

If Target.CountLarge > 1 Then Exit Sub



' Check to see if entry is made in column I after row 1 and is set to "Discharged"

If Target.Column = 9 And Target.Row > 1 And Target.Value = "Discharged" Then

Application.EnableEvents = False

' Copy columns A to I to Processed sheet in next available row

Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheets("Processed").Cells(Rows.Count, "A").End(xlUp).Offset(0, 0)

' Delete current row after copied

Rows(Target.Row).Delete

Application.EnableEvents = True

End If



End Sub

 

[NateSC]

I think when you call the following statement, it finds the last used row on "Processed" and pastes the data there.

Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheets("Processed").Cells(Rows.Count, "A").End(xlUp).Offset(0, 0)

But you really want it to be a row below the last used row. So you should Offset by 1 row (Offset (1,0)):

Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheets("Processed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Try that change and see if it works.

 

[EBe]

Ah perfect, that works now. Thank you so much!