How to create a new sheet based on student Id numbers

Report
Posts
1
Registration date
Monday April 19, 2021
Status
Member
Last seen
April 19, 2021
-
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
-
Hello,

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 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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

Sheets("Sheet1").Select
Header1 = Range("A1:J1")

For Each cell In Range("B2:B" & Range("B1").End(xlDown).Row)
    cell.EntireRow.Copy
    If Not SheetExists(cell.Value) Then
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = cell.Value
        Range("A1:J1").Value = Header1
    Else
        Sheets(cell.Value).Select
    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

NoSuchSheet:
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,
Trowa

Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.

Cheerio,
vcoolio.