Use VBA to Split Participant Responses into Separate Excel Sheets
Question details
The user wants to automate the process of creating individual worksheets for each participant from a Slido export, copying specific headers and response data from a master sheet into each new sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Processing a Slido export file where each participant's data is on a single row, and separating it so each person has their own worksheet containing their specific answers and score.
- Observed behavior
- Currently, all participant data is compiled in a single master sheet, requiring manual separation or a custom macro to distribute the data into individual sheets.
Ensure you save a backup of your original Slido export workbook and verify that the Developer tab is enabled in your spreadsheet software so you can run VBA macros.
Use a VBA Macro to Auto-Split Rows into Individual Sheets
Use a custom VBA script to loop through rows, generate new tabs for each participant, and copy over the necessary question headers and score data automatically.
This macro assumes your participant names are in column B, your question headers span from F1 to AB1, and the total score is in column E. It dynamically loops through every row of data, creates a new sheet named after the participant, and transposes their specific answers alongside the headers.
Press 'Alt + F11' on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.
In the VBA editor, click 'Insert' > 'Module' from the top menu bar to create a blank scripting window.
Paste the following code into the module window: Sub ExtractResponses() Dim ws As Worksheet Dim newWs As Worksheet Dim lastRow As Long Dim i As Long, j As Long Dim colStart As Long, colEnd As Long Dim participantName As String Dim questionHeaders As Range Set ws = ThisWorkbook.Sheets("Sheet1") Set questionHeaders = ws.Range("F1:AB1") colStart = questionHeaders.Column colEnd = colStart + questionHeaders.Columns.Count - 1 lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row For i = 2 To lastRow participantName = ws.Cells(i, "B").Value Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) newWs.Name = participantName For j = colStart To colEnd newWs.Cells(1, j - colStart + 1).Value = ws.Cells(1, j).Value newWs.Cells(2, j - colStart + 1).Value = ws.Cells(i, j).Value Next j newWs.Cells(3, 1).Value = "Total Correct" newWs.Cells(3, 2).Value = ws.Cells(i, "E").Value Next i End Sub
Close the VBA editor or press 'F5' while inside the code to run the macro. Your workbook will automatically generate a new sheet for every participant listed in the master sheet.
Automate Data Splitting with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to easily run scripts to separate participant data into multiple worksheets. It offers a familiar interface and excellent compatibility with Microsoft Excel files.
- 1. Open your File in WPS: Launch WPS Spreadsheet and open your Slido export file containing the participant data.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic' to open the macro environment.
- 3. Run the Script: Insert a new module, paste the VBA code provided above, and click the 'Run' button to automatically split the rows into separate sheets.
Frequently Asked Questions
What if my participant names contain invalid characters for sheet names?
Spreadsheet applications do not allow certain characters like brackets, slashes, asterisks, or question marks in sheet names. You should use a Find and Replace action to remove these characters from your participant name column before running the macro.
How can I prevent an error if two participants have the exact same name?
A standard macro will crash if it tries to create two sheets with identical names. You can modify the VBA script to append an incremental number (e.g., 'Name 1', 'Name 2') or ensure your source data includes a unique identifier like an email address or ID number in the name column.
How do I enable the Developer tab to run this macro?
Go to File > Options > Customize Ribbon, and check the box next to the 'Developer' option in the right pane. This will add the Developer tab to your main menu, giving you access to the Visual Basic editor.
Can I adapt this macro for columns other than F to AB?
Yes. In the provided VBA code, locate the line `Set questionHeaders = ws.Range("F1:AB1")` and change 'F1:AB1' to match the exact column range where your specific question headers are located.
