Sorting students into classes

Hi all

I did ask this a few days ago, but the thread kind of got distracted with my problems posting a file, so I'm going to re-ask my original question, as I see so many useful and helpful responses on here, I don't want to be the odd one out. Plus, I'm going mad not knowing the answer!

I've been trying to work this out for nearly a week now, and had absolutly no joy, but I'm sure it's much easier than I'm making it out to be.

I have a master list of overseas students visiting for language lessons. they have to be graded according to ability and then placed in classes, which can be high, medium or low level, and morning or afternoon, with 2 of each class running.

All I want to do it select the correct ability level from a drop down on the first page, and then the remaining sheets are then auto-populated with the correct data from the first sheet based on that selection.

Then, the class lists for the students are also completed with just their names in the same way.

I'm sure I just need to use the VLookup function (as well as the concentate function for the names), but I'm damned if I can figure out what I'm doing.

I've posted a link to the sheet to help explain what I have to try and get working.

http://h1.ripway.com/Squeaker/SummerRegisterblank.xls

Any help would really be tremendously appreciated, at the minute all the information has to be cut and pasted by hand, and it takes HOURS!

Thanks in advance to all and anyone that can help, and sorry for re-posting.

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.