How to create a new sheet based on student Id numbers

Closed
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 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Apr 23, 2021 at 09:28 AM
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

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
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

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

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
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.

Cheerio,
vcoolio.
0