Procedure Too Large - Compile Error

[JustJerry2C]

My code worked great until I finished it for 35 students. Now I get the procedure too large error, and I'm too much a novice in VBA for Excel to correctly figure this out.

I have Sheet 1 that teachers will input a last name and a first name for each student. Student 1 starts on row 9 all the way through 43. B9 is Last Name, C9 is First Name.
If they input a name, then the code is to change the associated report card tab and checklist tab for each student. Student one starts with worksheet 6 and 7, student 2 8/9, and so on.
Both a first name and last name must be entered to change the tab names. If not, the tab will revert back to a default name of S11 and S11C for student 1.... or S3535 and S3535C for student 35. I did this to prevent accidental same names for sheets.
Also, in column D, a 'Y' will be added that helps me out on another function to automatically create report cards, and the student name will be entered into a hidden cell on another sheet.

The code is the same for all 35 students, so I will post the sections for the first two students. I know somehow, as I've researched, I need to create a module to be able to call this out, but I am honestly clueless on how to do this, so any help would be appreciated.

 Private Sub Worksheet_Change(ByVal Target As Range)

 'Student 1 Auto Assign sheet name and student name on report card - check list - Grading sheets
 If Not Intersect(Target, Range("B9")) Is Nothing Then
     If Range("B9").Value = "" Then
         Range("B9").Value = 1
         Range("D9") = "N"
         Worksheets(6).Name = Worksheets(1).Range("C9") & Worksheets(1).Range("B9")  'Rename Student Report Card worksheet to Default Name
         Worksheets(7).Name = Worksheets(1).Range("C9") & Worksheets(1).Range("B9") & "C"    'Rename Student Checklist worksheet to default name
         Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
         Worksheets(2).Range("A70") = ""      'Remove Vocab Name
         If Range("C9").Value = "S1" Then
             Range("D9") = "N"
             Worksheets(6).Name = Worksheets(1).Range("C9") & "1"  'Rename Student Report Card worksheet to Student name
             Worksheets(7).Name = Worksheets(1).Range("C9") & "1C"   'Rename Student Checklist worksheet to Student name
             Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
             Worksheets(2).Range("A70") = ""      'Remove Vocab Name
         Else
             Range("D9") = "Y"
             Worksheets(6).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9")    'Rename Student Report Card worksheet to Student name
             Worksheets(7).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9") & " Check" 'Rename Student Checklist worksheet to Student name
             Range("A70").Value = Range("B9").Value & ", " & Range("C9").Value    'Assign Report Card Name & Checklist Name
             Worksheets(2).Range("A70") = Range("B9").Value & ", " & Range("C9").Value   'Assign Vocab Name
         End If
     End If
 End If
   
 If Not Intersect(Target, Range("C9")) Is Nothing Then
     If Range("C9").Value = "" Then
         Range("C9").Value = "S1"
         Range("D9") = "N"
         Worksheets(6).Name = Worksheets(1).Range("C9") & "1"  'Rename Student Report Card worksheet to Default Name
         Worksheets(7).Name = Worksheets(1).Range("C9") & "1C" 'Rename Student Checklist worksheet to default name
         Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
         Worksheets(2).Range("A70") = ""      'Remove Vocab Name
     Else
        If Range("B9").Value = "1" Then
             Range("D9") = "N"
             Worksheets(6).Name = "S1" & Worksheets(1).Range("B9")  'Rename Student Report Card worksheet to Default Name
             Worksheets(7).Name = "S1" & Worksheets(1).Range("B9") & "C"    'Rename Student Checklist worksheet to default name
             Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
             Worksheets(2).Range("A70") = ""      'Remove Vocab Name
         Else
             Worksheets(6).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9")    'Rename Student Report Card worksheet to Student name
             Worksheets(7).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9") & " Check" 'Rename Student Checklist worksheet to Student name
             Range("D9") = "Y"
             Range("A70").Value = Range("B9").Value & ", " & Range("C9").Value    'Assign Report Card Name & Checklist Name
             Worksheets(2).Range("A70") = Range("B9").Value & ", " & Range("C9").Value   'Assign Vocab Name
         End If
     End If
 End If
     'Student 2 Auto Assign sheet name and student name on report card - check list - Grading sheets
 If Not Intersect(Target, Range("B10")) Is Nothing Then
     If Range("B10").Value = "" Then
         Range("B10").Value = 2
         Range("D10") = "N"
         Worksheets(8).Name = Worksheets(1).Range("C10") & Worksheets(1).Range("B10")  'Rename Student Report Card worksheet to Default Name
         Worksheets(9).Name = Worksheets(1).Range("C10") & Worksheets(1).Range("B10") & "C"    'Rename Student Checklist worksheet to default name
         Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
         Worksheets(2).Range("A71") = ""      'Remove Vocab Name
         If Range("C10").Value = "S2" Then
             Range("D10") = "N"
             Worksheets(8).Name = Worksheets(1).Range("C10") & "2"  'Rename Student Report Card worksheet to Student name
             Worksheets(9).Name = Worksheets(1).Range("C10") & "2C"   'Rename Student Checklist worksheet to Student name
             Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
             Worksheets(2).Range("A71") = ""      'Remvoe Vocab Name
         Else
             Range("D10") = "Y"
             Worksheets(8).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10")    'Rename Student Report Card worksheet to Student name
             Worksheets(9).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10") & " Check" 'Rename Student Checklist worksheet to Student name
             Range("A71").Value = Range("B10").Value & ", " & Range("C10").Value    'Assign Report Card Name & Checklist Name
             Worksheets(2).Range("A71") = Range("B10").Value & ", " & Range("C10").Value   'Assign Vocab Name
         End If
     End If
 End If
   
 If Not Intersect(Target, Range("C10")) Is Nothing Then
     If Range("C10").Value = "" Then
         Range("C10").Value = "S2"
         Range("D10") = "N"
         Worksheets(8).Name = Worksheets(1).Range("C10") & "2"  'Rename Student Report Card worksheet to Default Name
         Worksheets(9).Name = Worksheets(1).Range("C10") & "2C" 'Rename Student Checklist worksheet to default name
         Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
         Worksheets(2).Range("A71") = ""      'Remove Vocab Name
     Else
         If Range("B10").Value = "2" Then
             Range("D10") = "N"
             Worksheets(8).Name = "S2" & Worksheets(1).Range("B10")  'Rename Student Report Card worksheet to Default Name
             Worksheets(9).Name = "S2" & Worksheets(1).Range("B10") & "C"    'Rename Student Checklist worksheet to default name
             Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
             Worksheets(2).Range("A71") = ""      'Remove Vocab Name
         Else
             Worksheets(8).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10")    'Rename Student Report Card worksheet to Student name
             Worksheets(9).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10") & " Check" 'Rename Student Checklist worksheet to Student name
             Range("D10") = "Y"
             Range("A71").Value = Range("B10").Value & ", " & Range("C10").Value    'Assign Report Card Name & Checklist Name
             Worksheets(2).Range("A71") = Range("B10").Value & ", " & Range("C10").Value   'Assign Vocab Name
         End If
     End If
 End If

 End Sub

 

[Macropod]

Cross-posted at: Procedure Too Large - Compile Error
Please read Mr Excel's policy on Cross-Posting in rule 13: Forum Rules

 

[JustJerry2C]

My apologies. I did not know posting on two, what I thought were independent, websites was a violation of this forum. I honestly was just desperate for a solution so that I didn't hold up a project I thought I could do.

 

[Macropod]

Had you read the rules as requested, you'd realize the whole point of the rule is that they are unrelated forums.