Forum Discussion
preynol5
Jan 24, 2025Copper Contributor
Moving and manipulating data between sheets
The organisation I work for is using Slido to assess candidate's knowledge, or lack of it. Slido produces an XLS file of what the responses were to a particular question. The XLS produced by Slido ha...
Kidd_Ip
Jan 26, 2025MVP
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