help with sorting student directory

ingle

New Member
Joined
Sep 3, 2006
Messages
2
I have a student directory that shows each kid by last name, first name,
teacher and phone number. It is sorted by last name. I need to make
seperate worksheets that places all kids (with phone numbers) under each
teacher in a list.

Any help will be appreciated.

Mark
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I have a student directory that shows each kid by last name, first name,
teacher and phone number. It is sorted by last name. I need to make
seperate worksheets that places all kids (with phone numbers) under each
teacher in a list.

Any help will be appreciated.

Mark

Mark,

Lets see if I get this right. You want to end up with a sheet for each teacher ( I assume there are fewer then 255)? On this sheet you want each student assigned to this teacher listed along with their phone numbers? You also want the student list on each teacher's sheet to automatically update each time a student is added?

Is this correct?

Perry
 
Upvote 0
I have a student directory that shows each kid by last name, first name,
teacher and phone number. It is sorted by last name. I need to make
seperate worksheets that places all kids (with phone numbers) under each
teacher in a list.

Any help will be appreciated.

Mark

Mark,

Just for fun see if this does what you want.

Rename three sheets on a blank workbook to:

Students
Johnson
Miller

Now goto the VBA editor (Alt+F11) and then under your project select 'Students'.

You should now have an empty window.

Copy and paste this code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
Dim SheetBottom As Integer
Dim TeacherSheet As String
Dim TeacherSheetBottom As Integer
Dim wb As String
Dim ws As Worksheet
Dim wt As Worksheet
Dim z As Integer

On Error GoTo EndMacro

wb = ThisWorkbook.Name

Set ws = Workbooks(wb).Worksheets("Students")

'Find last student entry

i = ws.Cells(Rows.Count, 1).End(xlUp).Row

'turn off events for worksheet changes
Application.EnableEvents = False

'clear all sheets except 'Students'

For Each wt In Worksheets

If wt.Name <> "Students" Then

'find last student on each teacher sheet

SheetBottom = Worksheets(wt.Name).Cells(Rows.Count, 1).End(xlUp).Row

'clear contents on each teacher sheet

Worksheets(wt.Name).Range("a1", "c" & SheetBottom).ClearContents

End If

Next wt

'start at row 4 and loop until last student

For z = 4 To i

'find teacher for each student
    TeacherSheet = ws.Cells(z, 4)

'find and update row number of last entry for teacher sheet
    TeacherSheetBottom = Workbooks(wb).Worksheets(TeacherSheet).Cells(Rows.Count, 1).End(xlUp).Row

'transfer students names and phone numbers to teachers
    Workbooks(wb).Worksheets(TeacherSheet).Range("a" & TeacherSheetBottom + 1) = ws.Range("a" & z)
    Workbooks(wb).Worksheets(TeacherSheet).Range("b" & TeacherSheetBottom + 1) = ws.Range("b" & z)
    Workbooks(wb).Worksheets(TeacherSheet).Range("c" & TeacherSheetBottom + 1) = ws.Range("c" & z)

Next z

EndMacro:

'turn events back on
Application.EnableEvents = True

End Sub

Now on the 'Student' sheet starting on row number 4 start entering your student information in the following format.

Column A - Lastname
Column B - Firstname
Column C - Phone Number
Column D - Teacher Name (Either Johnson or Miller)

Now if all goes well these students should now appear in the proper sheets either Johnson or Miller. This currently doesn't sort them but that would be easy to add.

Perry
 
Upvote 0
student directory

Perry,

That works - thank you very much. Since my VBA skills are very limited, I would like to beg for a little more assistance with the file. I found out the actual format for the directory is Last Name, First Name, Teacher, Grade and Phone. Other information (address, zip, email, etc.) follows but I don’t need to transfer it to the other worksheets.

As before, I would like the student names to be listed under the teacher tabs. I don’t know how to modify the visual basic to account for “grade” being between teacher and phone number. Also, I need to have the data alphabetically sorted on the worksheets.

Last question, can the original file start out on row 2 instead of row 4?

Thanks again for helping out on this.

Mark
 
Upvote 0
Re: student directory

Perry,

That works - thank you very much. Since my VBA skills are very limited, I would like to beg for a little more assistance with the file. I found out the actual format for the directory is Last Name, First Name, Teacher, Grade and Phone. Other information (address, zip, email, etc.) follows but I don’t need to transfer it to the other worksheets.

As before, I would like the student names to be listed under the teacher tabs. I don’t know how to modify the visual basic to account for “grade” being between teacher and phone number. Also, I need to have the data alphabetically sorted on the worksheets.

Last question, can the original file start out on row 2 instead of row 4?

Thanks again for helping out on this.

Mark

Mark,

When I get home I'll look at 'adding' these features. I already made a few changes on the example I have at home that includes converting all teacher names to upper case for searching purposes (Requires tab names in upper case) so it isn't particular on case for teacher names in the data, I already added sorting on last name for students.

One item to consider which I have been looking at is automatically creating the additional sheets (and naming them) if a teacher doesn't already have a created sheet.

Perry
 
Upvote 0
Re: student directory

Perry,

That works - thank you very much. Since my VBA skills are very limited, I would like to beg for a little more assistance with the file. I found out the actual format for the directory is Last Name, First Name, Teacher, Grade and Phone. Other information (address, zip, email, etc.) follows but I don’t need to transfer it to the other worksheets.

As before, I would like the student names to be listed under the teacher tabs. I don’t know how to modify the visual basic to account for “grade” being between teacher and phone number. Also, I need to have the data alphabetically sorted on the worksheets.

Last question, can the original file start out on row 2 instead of row 4?

Thanks again for helping out on this.

Mark

This starts looking on row 2 of sheet 'Students' and will transfer the lastname, firstname, grade, and phone number to the teacher sheets starting on row 3 which in the vba is listed as :

TeacherSheetStart = 3

You should be able to change this to whatever row you want to start at but I haven't tested all that well.

On the test sheets I have a row as a heading on each teacher sheet on row one and then TeacherSheetStart is use to skip one row and start the list of students on row 3.

So basically the flow is this:

1. Clear all teacher sheets starting at row 3 to the bottom row.

2. Starting at row 2 on 'Students' scan all students looking at column 3 for their teachers.

3. Transfer all results to the correct teacher sheet starting on row 3.

4. Sort each teacher sheet by lastname, then firstname.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

Dim SheetBottom As Integer

Dim TeacherSheet As String
Dim TeacherSheetStart As Integer
Dim TeacherSheetBottom As Integer
Dim wb As String
Dim ws As Worksheet
Dim wt As Worksheet
Dim z As Integer

On Error GoTo EndMacro

wb = ThisWorkbook.Name

Set ws = Workbooks(wb).Worksheets("Students")

i = ws.Cells(Rows.Count, 1).End(xlUp).Row

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

TeacherSheetStart = 3

For Each wt In Worksheets

    If wt.Name <> "Students" Then

    SheetBottom = Worksheets(wt.Name).Cells(Rows.Count, 1).End(xlUp).Row

    If SheetBottom > TeacherSheetStart Then

        Worksheets(wt.Name).Range("a" & TeacherSheetStart, "d" & SheetBottom).ClearContents

    End If

    End If

Next wt

For z = 2 To i

    TeacherSheet = UCase(ws.Cells(z, 3))

    If TeacherSheet <> "" Then
    
        TeacherSheetBottom = Workbooks(wb).Worksheets(TeacherSheet).Cells(Rows.Count, 1).End(xlUp).Row

        If TeacherSheetBottom < TeacherSheetStart - 1 Then TeacherSheetBottom = TeacherSheetStart - 1

        Workbooks(wb).Worksheets(TeacherSheet).Range("a" & TeacherSheetBottom + 1) = ws.Range("a" & z)
        Workbooks(wb).Worksheets(TeacherSheet).Range("b" & TeacherSheetBottom + 1) = ws.Range("b" & z)
        Workbooks(wb).Worksheets(TeacherSheet).Range("c" & TeacherSheetBottom + 1) = ws.Range("d" & z)
        Workbooks(wb).Worksheets(TeacherSheet).Range("d" & TeacherSheetBottom + 1) = ws.Range("e" & z)
           
    End If

Next z

For Each wt In Worksheets

    If wt.Name <> "Students" Then

        SheetBottom = Worksheets(wt.Name).Cells(Rows.Count, 1).End(xlUp).Row

        Worksheets(wt.Name).Range("a" & TeacherSheetStart, "d" & SheetBottom).Sort Key1:=wt.Range("a3" & TeacherSheetStart), Order1:=xlAscending, Key2:=wt.Range("b3" & TeacherSheetStart), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

    End If

Next wt

EndMacro:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Let me know if it works ok, like I have said, limited testing.

Perry
 
Upvote 0

Forum statistics

Threads
1,223,204
Messages
6,170,722
Members
452,351
Latest member
Giare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top