Macros

[cambellini]

I have created a worksheet for grading my students. Every year I have to copy the template sheet and rename it after each individual student. I have found a macros for creating new named worksheets. I have tried recording a macro of the repetitive process without success.
I also am looking for a macro that will return data (marks) from the student worksheet to a central worksheet where the class marks are collated. This would be possible with vlookup, but setting this is up would still be time consuming.
Any advise on this would be appreciated

 

[jbeaucaire]

1) What is the sheet name with all the student names listed? What column lists all the student names? What range of cells?

2) What is the name of the template you want used to create the student-named sheets?

3) Is the template sheet hidden (it should be)? Can we hide it again when we're done making copies?

4) Is there a spot on each student sheet where you want the student's name filled in?

 

[cambellini]

Thanks for the prompt response,

1) Sheet name is 'Student Names'. Column A, range A1:A40
2) Template sheet is called 'Individual'
3) The Template sheet is not hidden, but it could be hidden after making copies
4) The students name is in cells B4:C4 which is merged

 

[jbeaucaire]

Try this macro. If a particular student's sheet already exists, it will highlight the cell and give a message at the end of the routine.

Option Explicit

Sub AddStudents()
Dim RNG As Range, cell As Range, cnt As Long
Application.ScreenUpdating = False

Set RNG = Sheets("Student Names").Range("A1:A40").SpecialCells(xlCellTypeConstants, 2)
Sheets("Individual").Visible = True

For Each cell In RNG
    If Not Evaluate("ISREF('" & cell & "'!A1)") Then
        Sheets("Individual").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = cell
        Range("B4").Value = cell
    Else
        cnt = cnt + 1
        cell.Interior.ColorIndex = 3
    End If
Next cell

Sheets("Student Names").Activate
If cnt > 0 Then MsgBox "There were " & cnt & " names not turned into sheets due to sheet name conflicts." & vbLf & "Please check the highlighted cells and make sure those sheets do already exist."

Set Rng = Nothing
Sheets("Individual").Visible = False
Application.ScreenUpdating = True
End Sub

 

[cambellini]

That's brilliant, Thank you very much, this will save a lot of time for me.