Les classes de chaque professeur

Option Explicit
Sub TableauDuProfesseur()
'JBeaucaire  (8/24/2009)
Dim LR As Long, i As Long, r As Long, c As Long
Dim str As String, studpre As String, stud As String, t As Double

Sheets("timetable").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR
'On cree la feuille si necessaire
 str = Cells(i, "C").Text
    If str = "" Then Exit Sub
    If Not Evaluate("ISREF('" & str & "'!A1)") Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = str
        Call FormatSheet(str)
        Sheets("timetable").Activate
    End If
'on insere les données
'Chechons d'abord la colonne adequate
    'Si le le nom du jour se termine par "2", c'est qu'il est un aprés midi
    If InStr(Cells(i, "A"), "2") > 0 Then
        t = Cells(i, "B") + 0.25
    Else
        t = Cells(i, "B")
    End If
        Select Case t
            Case 0.7 To 0.999:       c = 1   '17:00
            Case 0.66 To 0.6999:     c = 2   '16:00
            Case 0.625 To 0.65:      c = 3   '15:00
            Case 0.58 To 0.62:       c = 4   '14:00
             Case 0.45 To 0.57:       c = 5   '11:00
              Case 0.41 To 0.44:       c = 6   '10:00
             Case 0.375 To 0.4:       c = 7   '9:00
            Case 0.33 To 0.374:      c = 8   '8:00
        End Select
    'Cherchons ensuite la ligne adequate
        Select Case LCase(Left(Cells(i, "A"), 3))
            Case "mon":     r = 3
            Case "tue":     r = 4
            Case "wed":     r = 5
            Case "thu":     r = 6
            Case "fri":     r = 7
            Case "sat":     r = 8
        End Select
    'copions les donnés dans les cellules adequates
        Sheets(str).Cells(r, c).Value = Cells(i, "D")
      
Next i
End Sub


Sub FormatSheet(str As String)
    Range("A1:I1").HorizontalAlignment = xlCenterAcrossSelection
    Range("A1:I1").Font.FontStyle = "Bold"
    Range("A1:I1").Font.ColorIndex = 3
    Range("A1") = str
    Range("A2") = "18~17"
    Range("B2") = "17~16"
    Range("C2") = "16~15"
    Range("D2") = "15~14"
    Range("E2") = "12~11"
    Range("F2") = "11~10"
    Range("G2") = "10~09"
    Range("H2") = "09~08"
    Range("I3") = "Lundi"
    Range("I4") = "Mardi"
    Range("I5") = "Mercredi"
    Range("I6") = "Jeudi"
    Range("I7") = "Vendredi"
    Range("I8") = "Samedi"
    Columns("A:A").EntireColumn.AutoFit
    Range("A2:I8").Borders.LineStyle = xlContinuous
    Range("A2:I8").Font.Name = "tahoma"
     Range("A2:I8").Font.Size = "12"
end sub