Make 1 list of names out of multiple using VBA

[VikingLink]

For a school trip, I need to be able to make 1 long list with all pupils. I'd like it to feature their first name in column A, their last name in column B and their class in column C.
My starting point is one excel file with a separate sheet per class, their class name at the top and their complete name in a single cell, with their last name first and their first name last. (See example below).

I managed to find some formules to separate out their names and class (=TRIM(RIGHT(SUBSTITUTE(B5;" ";REPT(" ";LEN(B5)));LEN(B5))) for their first name, =LEFT(B5;FIND("[";SUBSTITUTE(B5;" ";"[";LEN(B5)-LEN(SUBSTITUTE(B5;" ";""))))-1) for their last name, and =TRIM(RIGHT(SUBSTITUTE($A$1;" ";REPT(" ";LEN($A$1)));LEN($A$1))) for their class). I placed this formula in cells C5 to C7, and then dragged them down.

To get them into one list, I use the following VBA, which seems to work.

Sub Copy_Range_From_Sheets()
'Modified  10/12/2021  6:36:48 PM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = 6

For i = 2 To Lastrow
    ans = Sheets("Master").Cells(i, 1).Value
   
    With Sheets(ans)
        .Range("C5:E35").Copy
        Sheets("Summary").Cells(Lastrowa, 1).PasteSpecial xlPasteValues
        Lastrowa = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End With
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You tried to use a sheet name that does not exist" & vbNewLine & "Or we had another problem"
Application.ScreenUpdating = True
End Sub

The problem is, that as I have 15 classes, it's a tedious job to copy the formulas and drag them down through the column for every pupil, so I was looking for a VBA to copy this formula to all 15 sheets, and drag it until the end of the list of pupils. I ran into an issue with this, as the " " in the formulas keeps giving me an error message.

Can someone help me with a VBA that will copy the formulas into all sheets (except for the Master sheet and the Summary sheet) and drag it down to get every pupil's name? The class sheets are always the same, the only thing that changes is the amount of pupils. This is normally limited to 26.

 

[VikingLink]

Thanks, yet again.