Moving and manipulating data between sheets

Try this in VBA:

Sub ExtractResponses()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim colStart As Long
    Dim colEnd As Long
    Dim participantName As String
    Dim questionHeaders As Range
    Dim questionResponses As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name
    
    ' Identify the range containing the questions
    Set questionHeaders = ws.Range("F1:AB1") ' Adjust if needed
    colStart = questionHeaders.Column
    colEnd = colStart + questionHeaders.Columns.Count - 1
    
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each participant
    For i = 2 To lastRow ' Assuming row 1 has headers
        participantName = ws.Cells(i, "B").Value
        Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        newWs.Name = participantName
        
        ' Copy question headers to new sheet
        For j = colStart To colEnd
            newWs.Cells(1, j - colStart + 1).Value = ws.Cells(1, j).Value
        Next j
        
        ' Copy question responses to new sheet
        For j = colStart To colEnd
            newWs.Cells(2, j - colStart + 1).Value = ws.Cells(i, j).Value
        Next j
        
        ' Copy total number of correct answers to new sheet (optional)
        newWs.Cells(3, 1).Value = "Total Correct"
        newWs.Cells(3, 2).Value = ws.Cells(i, "E").Value
        
    Next i
End Sub