copy entire row to another sheet conditionally

[sada]

hi every 1
here is my problem
sheet1 is "main register" . sheet2 is "expelled" and sheet3 is "active stu" when any cell in col E is = "expelled" i want the entire row to be copied to sheet2 AND also if i change a col E cell (lets say E12) from "expelled" to "active stu" it should remove the row from sheet2 and be copied it to sheet3. i prefer it without any button. just to be automatic.

pls instruct me in little detail. i am not very familar with VB.

[MatrixMan]

hi sada ... this can be done fairly easily ... post a sample of your book showing how your sheets are set up & I'll take a look for you ..

[sada]

hi sada ... this can be done fairly easily ... post a sample of your book showing how your sheets are set up & I'll take a look for you ..

Hey thanks a lot

Here is a sample work file.


Sada
[email protected]

[MatrixMan]

Here you go:

Private Sub cmdCopyToSummarySheets_Click()
Dim intFirstDataRow As Integer, intLastCol As Integer
Dim lngLastDataRow As Integer
Dim strMainSheet As String, strWaitSheet As String, strLeftSheet As String, strActiveSheet As String, strExpelledSheet As String
Dim i As Long, j As Long, k As Long
Dim wsh As Worksheet

Const strID_Col = "A"
Const strStatus_Col = "F"

    strMainSheet = Me.Name
    strWaitSheet = Sheet2.Name
    strActiveSheet = Sheet3.Name
    strExpelledSheet = Sheet4.Name
    strLeftSheet = Sheet5.Name
    
    intFirstDataRow = 2
    intLastCol = Me.Range("A1").End(xlToRight).Column
    lngLastDataRow = Me.Range(strStatus_Col & 1).End(xlDown).Row
    If lngLastDataRow = Me.Cells.Rows.Count Then
        lngLastDataRow = 2
    Else
        'first clear out all previously copied data in the other sheets:
        For Each wsh In Worksheets
            If wsh.Name <> Me.Name Then
                wsh.Range("A" & intFirstDataRow & ":A" & wsh.Range("A1").End(xlDown).Row).EntireRow.Delete shift:=xlUp
            End If
        Next wsh
        'now run down the status list & copy to the relevent sheet:
        For i = intFirstDataRow To lngLastDataRow
            Select Case UCase(Me.Range(strStatus_Col & i).Text)
                Case "ACTIVE"
                    Me.Range(strStatus_Col & i).EntireRow.Copy Destination:= _
                        Worksheets(strActiveSheet).Rows(Worksheets(strActiveSheet).Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1)
                Case "WAITING"
                    Me.Range(strStatus_Col & i).EntireRow.Copy Destination:= _
                        Worksheets(strWaitSheet).Rows(Worksheets(strWaitSheet).Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1)
                Case "LEFT"
                    Me.Range(strStatus_Col & i).EntireRow.Copy Destination:= _
                        Worksheets(strLeftSheet).Rows(Worksheets(strLeftSheet).Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1)
                Case "EXPELLED"
                    Me.Range(strStatus_Col & i).EntireRow.Copy Destination:= _
                        Worksheets(strExpelledSheet).Rows(Worksheets(strExpelledSheet).Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1)
                Case Else
                    Exit For
            End Select
        Next i
    End If
    MsgBox ("Update Complete - " & i - intFirstDataRow + 1 & " records processed.")
End Sub

Code is also in the attached workbook (Excel 2003) and is linked to the button in the main sheet. Hope that helps.