Creating Separate Excel Sheets for Each Student with VBA

This is Post 3 in our 4-part series on Automating Student Feedback Processing with VBA. In Post 2, we cleaned and standardised student names. Now we're ready for the most powerful step: automatically creating separate worksheets for each student.

The Manual Nightmare: Creating 100 Worksheets by Hand

Picture this: You have a spreadsheet with 100 students and their peer feedback responses. Your goal is to give each student their own worksheet containing only their feedback—so they can review it privately without seeing others' data.

The manual process looks like this:

1. Right-click the sheet tab → Insert → Worksheet
2. Rename the new sheet with the student's name
3. Go back to the source data
4. Filter column A to show only that student's rows
5. Copy the filtered data
6. Paste into the new worksheet
7. Clear the filter
8. Repeat 99 more times without making a single mistake

Time per student: 60-90 seconds if you're quick and don't make errors
Total time for 100 students: 100-150 minutes (1.7 to 2.5 hours)

"I tried doing this manually for 50 students last term. After an hour, I was only halfway through—and I'd accidentally mixed up two students' data. I had to start over. Never again."

The VBA Solution: Create Excel Sheet for Each Student Automatically

VBA can automate this entire process. Here's what happens when you run the macro:

1. Scans your data column (e.g., student names in Column A)
2. Extracts all unique values (100 unique student names)
3. For each unique student:
   • Creates a new worksheet
   • Names it after the student
   • Copies the header row
   • Copies only that student's data rows
   • Formats it professionally
4. Repeats for all students in seconds

Time with VBA: 30 seconds for any number of students
Error rate: Zero (automation doesn't mix up names)

When to Use This Technique

This automation works for any scenario where you need to split data into multiple sheets based on a category:

• ✅ Student feedback: Individual sheets per student (our case study)
• ✅ Sales reports: Separate sheets by region, product, or salesperson
• ✅ Client data: Individual sheets per client for distribution
• ✅ Survey responses: Split by department, location, or demographic
• ✅ Project tracking: Separate sheets per project or team member
• ✅ Inventory management: Split by warehouse, category, or supplier

The key requirement: your data has a column containing the category you want to split by (names, regions, IDs, etc.).

Complete VBA Code: Create Separate Worksheets for Unique Values

Here's the complete, production-ready VBA code to create Excel sheets for each unique value in a column. This is the exact code from our free template.

Sub CreateSheetPerUniqueValue()
    ' Create Sheet Per Unique Value - vbacode.io
    ' Automatically creates separate worksheets for each unique value in a column
    ' Perfect for: Student sheets, client reports, regional data splits

    Dim wsSource As Worksheet
    Dim wsNew As Worksheet
    Dim uniqueValues As Collection
    Dim cell As Range
    Dim uniqueValue As Variant
    Dim lastRow As Long
    Dim dataRange As Range
    Dim headerRow As Range
    Dim targetColumn As Long
    Dim sheetExists As Boolean
    Dim ws As Worksheet

    ' Configuration
    Set wsSource = ActiveSheet
    targetColumn = 1  ' Column A - change to your target column (1=A, 2=B, etc.)

    ' Error handling
    On Error GoTo ErrorHandler

    ' Turn off screen updating for better performance
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' Find last row with data
    lastRow = wsSource.Cells(wsSource.Rows.Count, targetColumn).End(xlUp).Row

    ' Validate data exists
    If lastRow < 2 Then
        MsgBox "No data found. Make sure your data starts in row 2 (row 1 should be headers).", vbExclamation
        GoTo CleanUp
    End If

    ' Store header row
    Set headerRow = wsSource.Rows(1)

    ' Get unique values from target column
    Set uniqueValues = New Collection
    On Error Resume Next  ' Ignore duplicate errors
    For Each cell In wsSource.Range(wsSource.Cells(2, targetColumn), wsSource.Cells(lastRow, targetColumn))
        If Not IsEmpty(cell.Value) Then
            uniqueValues.Add cell.Value, CStr(cell.Value)  ' Key prevents duplicates
        End If
    Next cell
    On Error GoTo ErrorHandler

    ' Validate unique values found
    If uniqueValues.Count = 0 Then
        MsgBox "No unique values found in column " & Chr(64 + targetColumn) & ".", vbExclamation
        GoTo CleanUp
    End If

    ' Create sheets for each unique value
    For Each uniqueValue In uniqueValues
        ' Generate safe sheet name (Excel limit: 31 characters, no special characters)
        Dim sheetName As String
        sheetName = Left(CleanSheetName(CStr(uniqueValue)), 31)

        ' Check if sheet already exists
        sheetExists = False
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = sheetName Then
                sheetExists = True
                Exit For
            End If
        Next ws

        ' Create new sheet or clear existing one
        If sheetExists Then
            Set wsNew = ThisWorkbook.Worksheets(sheetName)
            wsNew.Cells.Clear
        Else
            Set wsNew = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            wsNew.Name = sheetName
        End If

        ' Copy header row
        headerRow.Copy wsNew.Range("A1")

        ' Copy filtered data
        Dim destRow As Long
        destRow = 2

        For Each cell In wsSource.Range(wsSource.Cells(2, targetColumn), wsSource.Cells(lastRow, targetColumn))
            If cell.Value = uniqueValue Then
                ' Copy entire row
                wsSource.Rows(cell.Row).Copy wsNew.Rows(destRow)
                destRow = destRow + 1
            End If
        Next cell

        ' Format new sheet
        With wsNew
            .Columns.AutoFit
            .Rows(1).Font.Bold = True
            .Rows(1).Interior.Color = RGB(68, 114, 196)  ' Blue header
            .Rows(1).Font.Color = RGB(255, 255, 255)  ' White text
        End With
    Next uniqueValue

    ' Success message
    MsgBox "Success! Created " & uniqueValues.Count & " worksheets." & vbCrLf & vbCrLf & _
           "Each sheet contains data for one unique value from column " & Chr(64 + targetColumn) & ".", vbInformation

CleanUp:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Error: " & Err.Description & vbCrLf & vbCrLf & _
           "Please check your data and try again.", vbCritical
End Sub

Function CleanSheetName(rawName As String) As String
    ' Remove invalid characters for sheet names: / \ ? * : [ ]
    Dim cleanName As String
    cleanName = rawName
    cleanName = Replace(cleanName, "/", "-")
    cleanName = Replace(cleanName, "\", "-")
    cleanName = Replace(cleanName, "?", "")
    cleanName = Replace(cleanName, "*", "")
    cleanName = Replace(cleanName, ":", "-")
    cleanName = Replace(cleanName, "[", "(")
    cleanName = Replace(cleanName, "]", ")")
    CleanSheetName = Trim(cleanName)
End Function

How to Use: Step-by-Step Setup Instructions

Step 1: Prepare Your Data

Before running the macro, ensure your data is structured correctly:

• Row 1: Header row (column names like "Student Name", "Feedback", "Score")
• Row 2 onwards: Your data
• Column A (or whichever column you choose): Contains the category to split by
• Data cleaned: If splitting by names, make sure you've cleaned and standardised them first (see Post 2)

Step 2: Open the VBA Editor

1. Open your Excel workbook
2. Press Alt + F11 (Windows) or Fn + Option + F11 (Mac)
3. In the VBA Editor, click Insert → Module
4. Copy the entire code above and paste it into the module window

Step 3: Configure the Target Column

Find this line near the top of the code:

targetColumn = 1  ' Column A - change to your target column

Change the number based on which column contains your categories:

• 1 = Column A (Student Name)
• 2 = Column B (Region)
• 3 = Column C (Client ID)
• And so on...

Step 4: Run the Macro

1. Close the VBA Editor (or press Alt + Q)
2. Make sure you're on the source data sheet (the sheet with all your data)
3. Press Alt + F8 (Windows) or Fn + Option + F8 (Mac)
4. Select CreateSheetPerUniqueValue
5. Click Run

Watch as Excel automatically creates all the worksheets in seconds!

Understanding the Code: How It Works Step-by-Step

Step 1: Extract Unique Values Using Collections

Set uniqueValues = New Collection
On Error Resume Next
For Each cell In wsSource.Range(wsSource.Cells(2, targetColumn), wsSource.Cells(lastRow, targetColumn))
    If Not IsEmpty(cell.Value) Then
        uniqueValues.Add cell.Value, CStr(cell.Value)
    End If
Next cell
On Error GoTo ErrorHandler

Why use Collections? A VBA Collection automatically prevents duplicates when you use the second parameter (the "key"). Here's what happens:

• First "Sarah Smith" encountered → Added to collection
• Second "Sarah Smith" encountered → Error raised (duplicate key) → Ignored by On Error Resume Next
• Result: Collection contains only unique student names

Alternative approach (Dictionary): You could also use Scripting.Dictionary which has an .Exists method, but Collection is simpler and doesn't require a reference to Microsoft Scripting Runtime.

Step 2: Loop Through Unique Values

For Each uniqueValue In uniqueValues
    ' Create worksheet for this unique value
Next uniqueValue

This loop runs once for each unique student name. For 100 students, it runs 100 times—creating 100 worksheets.

Step 3: Handle Sheet Naming Rules

sheetName = Left(CleanSheetName(CStr(uniqueValue)), 31)

Excel has strict rules for worksheet names:

• 31 character maximum: Longer names get truncated
• Invalid characters: Cannot include / \ ? * : [ ]
• Must be unique: No two sheets can have the same name

The CleanSheetName function handles this by:

1. Replacing slashes with hyphens (/ → -)
2. Removing question marks and asterisks
3. Converting brackets to parentheses ([ → ()
4. Trimming whitespace

Step 4: Check if Sheet Already Exists

sheetExists = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = sheetName Then
        sheetExists = True
        Exit For
    End If
Next ws

Why check? If you run the macro twice, it shouldn't fail or create duplicate sheets. Instead:

• If sheet exists → Clear it and reuse it
• If sheet doesn't exist → Create a new one

This makes the macro safe to re-run if your source data changes.

Step 5: Copy Filtered Data

For Each cell In wsSource.Range(wsSource.Cells(2, targetColumn), wsSource.Cells(lastRow, targetColumn))
    If cell.Value = uniqueValue Then
        wsSource.Rows(cell.Row).Copy wsNew.Rows(destRow)
        destRow = destRow + 1
    End If
Next cell

This loop scans every row in the source data:

• If the student name matches → Copy the entire row to the new sheet
• If not → Skip it

Performance note: For very large datasets (10,000+ rows), you might consider using AutoFilter + SpecialCells(xlCellTypeVisible) for faster copying. The current approach prioritises simplicity and works well for datasets up to 5,000 rows.

Step 6: Format the New Sheet

With wsNew
    .Columns.AutoFit
    .Rows(1).Font.Bold = True
    .Rows(1).Interior.Color = RGB(68, 114, 196)  ' Blue header
    .Rows(1).Font.Color = RGB(255, 255, 255)  ' White text
End With

Professional formatting makes the output immediately usable:

• AutoFit: Adjusts column widths so all data is visible
• Bold headers: Makes it clear which row contains column names
• Colour-coded header: Blue background with white text for visual appeal

Troubleshooting: Common Issues & Solutions

Problem: "No unique values found"

Cause: The target column is empty or contains only blank cells.

Solution:

• Check targetColumn is set to the correct column number
• Ensure your data starts in row 2 (row 1 should be headers)
• Verify the column isn't completely empty

Problem: Created 127 sheets instead of 100

Cause: Your data has inconsistent names (extra spaces, different capitalisation).

Solution:

• Go back to Post 2: Data Cleanup
• Run the name standardisation macro first
• Then run this sheet creation macro

Problem: "The sheet name is not valid"

Cause: A student name contains invalid characters like / or ?

Solution: The CleanSheetName function should handle this automatically. If you still get this error:

• Check if any names are longer than 31 characters (they'll be truncated)
• Look for unusual characters in names (emojis, symbols)
• Manually clean those specific names in your source data

Problem: Macro runs slowly (takes minutes)

Cause: Very large dataset or screen updating is still on.

Solution:

• Verify Application.ScreenUpdating = False is at the top of the code
• For datasets over 10,000 rows, consider filtering before copying (see advanced optimization below)

Problem: Sheets created but data is missing

Cause: Hidden rows or filtered data in the source sheet.

Solution:

• Clear all filters on the source sheet before running the macro
• Unhide all rows: Select all → Right-click → Unhide

Advanced: How to Create 100 Excel Sheets at Once with Custom Logic

Customization 1: Split by Multiple Columns

What if you want to create sheets based on two columns? For example, "Student Name + Class" so Sarah Smith from Class A and Sarah Smith from Class B get separate sheets?

Modification:

' Configuration
nameColumn = 1  ' Column A = Student Name
classColumn = 2  ' Column B = Class

' Build unique key
For Each cell In wsSource.Range(wsSource.Cells(2, nameColumn), wsSource.Cells(lastRow, nameColumn))
    If Not IsEmpty(cell.Value) Then
        uniqueKey = cell.Value & "_" & cell.Offset(0, 1).Value  ' Name + Class
        uniqueValues.Add uniqueKey, uniqueKey
    End If
Next cell

Customization 2: Add Sheet Numbering

Excel's 31-character limit means "Christopher Anderson-Smith" gets truncated. Add a counter to distinguish truncated names:

Dim sheetCounter As Integer
sheetCounter = 1

For Each uniqueValue In uniqueValues
    sheetName = Left(CleanSheetName(CStr(uniqueValue)), 28)  ' Leave room for counter
    sheetName = sheetName & "_" & Format(sheetCounter, "000")  ' Add _001, _002, etc.
    sheetCounter = sheetCounter + 1
    ' ... rest of code
Next uniqueValue

Customization 3: Copy Only Specific Columns

If your source data has 20 columns but you only want 5 columns in the individual sheets:

' Instead of copying entire row:
' wsSource.Rows(cell.Row).Copy wsNew.Rows(destRow)

' Copy specific columns only (A, C, D, F):
wsNew.Cells(destRow, 1).Value = wsSource.Cells(cell.Row, 1).Value  ' Col A
wsNew.Cells(destRow, 2).Value = wsSource.Cells(cell.Row, 3).Value  ' Col C
wsNew.Cells(destRow, 3).Value = wsSource.Cells(cell.Row, 4).Value  ' Col D
wsNew.Cells(destRow, 4).Value = wsSource.Cells(cell.Row, 6).Value  ' Col F
destRow = destRow + 1

Real-World Impact: Time Savings from Automation

Let's calculate the ROI for our student feedback case study:

Key insight: The VBA time stays roughly constant regardless of dataset size. Manual time scales linearly with the number of sheets needed.

Download the Free Template: Create Sheet Per Unique Value

Skip the copy-paste setup. We've packaged this exact code into a free, ready-to-use VBA template.

What's Next: Adding Charts & Statistics

You've now automated the most time-consuming step: creating individual worksheets. But we're not done yet.

In Post 4 (coming Wednesday), we'll complete the automation by:

• Generating bar charts showing rating distributions on each sheet
• Calculating average scores and summary statistics
• Exporting each sheet as a PDF for distribution (Mac-compatible approach)
• Combining all 3 steps (cleanup + sheets + charts) into one Pro template

This is where the automation becomes truly production-ready—turning raw survey data into polished, distributable feedback reports with one click.

Need Custom VBA Code for Your Specific Use Case?

The free template works great if your data structure is similar to our example. But what if you need:

• Split by multiple columns (e.g., Student Name + Class)
• Copy only specific columns to each sheet
• Custom formatting or conditional logic
• Integration with existing macros or databases

Coming Wednesday: Post 4 - Automating Survey Data Visualization with Charts & Statistics

The final piece of the puzzle: turn those individual sheets into polished reports with automated charts and summary statistics. Don't miss it!