How to create a new sheet based on student Id numbers

bookman35 Posts 1 Registration date Monday April 19, 2021 Status Member Last seen April 19, 2021 - Apr 19, 2021 at 01:15 PM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Apr 23, 2021 at 09:28 AM

I am trying to find a code that can make a new sheet based on my students name and ID number. I want to be able for the program to copy the data from their tests such as how well they did on the tagged concepts. I have all the data, I just want to be able to have a program make a new sheet for every name and then copy the information tied to their name into that new sheet. I have attached a picture of what the data set looks like.

System Configuration: Windows / Chrome 90.0.4430.72

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 22, 2021 at 11:39 AM
Hi Bookman,

I don't see any names in your screenshot. I also don't know how your sheet is called. So for the code below to work I made the following assumptions:
  • The sheet with all the data is called Sheet1.
  • The column on Sheet1 with all the names is column B.

Sub RunMe()
Dim Header1 As Variant

Header1 = Range("A1:J1")

For Each cell In Range("B2:B" & Range("B1").End(xlDown).Row)
    If Not SheetExists(cell.Value) Then
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = cell.Value
        Range("A1:J1").Value = Header1
    End If
    Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial
Next cell
End Sub

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet

If Len(Sheets(SheetName).Name) > 0 Then
    SheetExists = True
    Exit Function
End If

End Function

To change the sheet name in the code to match your, you can do so on code line 4.
To change the column letter where the names are located, you can do so on code line 7.

Best regards,

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Apr 23, 2021 at 09:28 AM
Hello Bookman,

If you supply a sample of your actual workbook, it will make solving this for you much, much simpler.

Please upload a sample of your workbook to a free file sharing site such as WeTransfer or Drop Box then post the link to your file back here. Make sure that the sample is an exact replica of your workbook and use dummy data for names or any other more sensitive data.

TrowaD (and any other helpers who may drop in) will be very happy to have something to work/test with.

Thank you Bookman.