Copy a range of cells in multiple sheets

Click the button and tell me if this is what you were after:

Marking Sheet Template.xlsm

The code:

Sub StudentMarks()

Dim ws1 As Worksheet
Dim wsTemplate As Worksheet
Dim rngName As Range
Dim rngStudents As Range
Dim calcMode As Long

Set ws1 = ActiveWorkbook.Sheets("StudentList")
Set wsTemplate = ActiveWorkbook.Sheets("Student")

   With Application
      calcMode = .Calculation
      .Calculation = xlCalculationManual
      .ScreenUpdating = False
      .EnableEvents = False
   End With

      With ws1
         Set rngStudents = .Range("C2", .Range("C2").End(xlDown))
      End With

      For Each rngName In rngStudents
         wsTemplate.Copy After:=Sheets(Sheets.Count)
         Set wsCurr = ActiveSheet
            wsCurr.Name = CStr(rngName)
            wsCurr.Range("B2").Value = rngName
      Next

    With Application
      .Calculation = calcMode
      .ScreenUpdating = True
      .EnableEvents = True
   End With

End Sub

Would you be open to me adding a 'Get Marks' button to set alongside the existing? The grades are not available at run time of the routine I provided, so I think it makes sense to separate them. Let me know.

Ok, so you will click the top Create button to get your worksheets created. Once you have completed your marks, you will click the red 'Get Marks' button, which will send the "C20" value for each student to the Marks column corresponding to their name on the 'StudentList' worksheet.

For a quick test, give Nathan a mark and then click the red button. You will see his mark appear with zeros for all other students (because you have not completed theirs yet).

Spreadsheet Here >> Marking Sheet Template.xlsm

'GetMarks' code:

Sub GetMarks()

Dim ws1 As Worksheet
Dim rngName As Range
Dim rngStudents As Range
Dim calcMode As Long

Set ws1 = ActiveWorkbook.Sheets("StudentList")

   With Application
      calcMode = .Calculation
      .Calculation = xlCalculationManual
      .ScreenUpdating = False
      .EnableEvents = False
   End With

      With ws1
         Set rngStudents = .Range("C2", .Range("C2").End(xlDown))
      End With

      For Each rngName In rngStudents
         rngName.Offset(0, 1).Value = Sheets(CStr(rngName)).Range("C20").Value
      Next

   With Application
      .Calculation = calcMode
      .ScreenUpdating = True
      .EnableEvents = True
   End With

End Sub

Interestingly, the calculation mode did not tick back to automatic. Hmmmm. I have forced Automatic upon termination of the code, which should get you back to what you are used to.

One more time here >> Marking Sheet Template.xlsm

In the future and to extend your general excel knowledge, what you experienced is the result of an xlCalculationManual setting. What you are used to is xlCalculationAutomatic. You can always change these settings via:

Office Button >> Excel Options >> Formulas >> 'Calculation Options' >> 'Workbook Calculation' >> 'Automatic'