Sorting students into classes

Try this event driven macro

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim TargetSheet As Worksheet
Dim TargetRow As Integer

For Each Cell In Target
    If Cell.Column = 9 Then
        Set TargetSheet = Sheets(Cell.Value)
        If TargetSheet.Cells(9, 1) = "" Then
            TargetRow = 9
        Else
            TargetRow = TargetSheet.Cells(29, 1).End(xlUp).Row + 1
        End If
        TargetSheet.Cells(TargetRow, 1) = Cells(Cell.Row, 5)
        TargetSheet.Cells(TargetRow, 2) = Cells(Cell.Row, 6)
        TargetSheet.Cells(TargetRow, 3) = Cells(Cell.Row, 2)
        TargetSheet.Cells(TargetRow, 4) = Cells(Cell.Row, 3)
        TargetSheet.Cells(TargetRow, 5) = Cells(Cell.Row, 8)
        TargetSheet.Cells(TargetRow, 6) = Cells(Cell.Row, 1)
    End If
Next Cell
End Sub

Open up the VBA editor by hitting ALT F11

Select the Sheet1(Register) tab.

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.



This slightly alternative more sophisticated macro will allow you to change your mind by redoing the entire list every time a single row changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim TargetSheet As Worksheet
Dim Sheet As Worksheet
Dim TargetRow As Integer

For Each Sheet In Sheets
    If InStr(Sheet.Name, "High") > 0 Or InStr(Sheet.Name, "Med") > 0 Or InStr(Sheet.Name, "Low") > 0 Then
        Sheet.Range(Sheet.Cells(9, 1), Sheet.Cells(28, 6)).ClearContents
    End If
Next Sheet


For Each Cell In Range(Cells(6, 9), Cells(65536, 9).End(xlUp))
    Set TargetSheet = Sheets(Cell.Value)
    If TargetSheet.Cells(9, 1) = "" Then
        TargetRow = 9
    Else
        TargetRow = TargetSheet.Cells(29, 1).End(xlUp).Row + 1
    End If
    TargetSheet.Cells(TargetRow, 1) = Cells(Cell.Row, 5)
    TargetSheet.Cells(TargetRow, 2) = Cells(Cell.Row, 6)
    TargetSheet.Cells(TargetRow, 3) = Cells(Cell.Row, 2)
    TargetSheet.Cells(TargetRow, 4) = Cells(Cell.Row, 3)
    TargetSheet.Cells(TargetRow, 5) = Cells(Cell.Row, 8)
    TargetSheet.Cells(TargetRow, 6) = Cells(Cell.Row, 1)
Next Cell
End Sub

You need to change your Mid to Med in the tab names for the macros to work.