Master schedule to individual schedule

Hello! I run an after-school program that students register for in order to participate. I've created macros to help assign classes, but once they are assigned, I would like to run a macro that takes information from the master schedule and generates individual schedules for each student that I can print our and send home.

In the attached spreadsheet are two tabs: one is the master schedule ("SUN Student Class Schedule"), and the other is a template of the format I'd like the individual schedules to take. The individual schedules contain student name, teacher, classes, and mode of transportation.

My idea was to run a macro that creates a new individual schedule (either in the Template tab or in a new tab), and then copy/pastes the data from each row in the master schedule to the newly created schedule.

I can create a macro to do it once, but can't figure out how to replicate that macro for every single row without doing it myself first.

I have also though of creating many empty individual schedules and having the display cells reference each unique row, but again can't figure out how to do it without typing in each function. If I were to create a template individual schedule and copy/paste, the fill-out function would skip many rows on the master schedule.

Any ideas and suggestions are greatly appreciated! Thank you very much for your help.

Everett

Take a look at the attached spreadsheet. go to your template tab.. then use the drop down box to select the student.

Also, I added a column on your first tab.. at the end.. to copy the first name of your students. You can hide this column. It is needed since the first name of your students is BEHIND the lookup value.

Please us this spreadsheet. I noticed you had some embedded code that resulted in not allowing the drop down box to function properly.

Originally Posted by optomyst

Please us this spreadsheet. I noticed you had some embedded code that resulted in not allowing the drop down box to function properly.

That is an ingenious idea! Thank you very much for the help and inspiration.

I have the code finished and am running into one last issue.

I need the macro to run once for each row of information, something on the order of 225 times. I tried just copying the code 225 times in the macro, but it says the procedure is too long.

Is there a way to loop the macro to run a number of times based on a cell value?

Here is the code:

Dim newsht As Worksheet, ws As Worksheet
Dim ivalue As String

ivalue = Sheets("SUN Student Class Schedule").Range("C5").Value
For Each ws In Worksheets
    If ws.Name = ivalue Then
        MsgBox ("There is already a sheet with the name " & "*" & ivalue & "*")
        Exit Sub
    End If
Next ws

Set newsht = Worksheets.Add
With newsht
    .Move After:=Sheets(Sheets.Count)
    .Name = ivalue
End With

Sheets("Template").UsedRange.Copy Destination:=newsht.Range("A1")
newsht.Range("M3").Value = ivalue
Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Template").UsedRange.Copy

Sheets("SUN Student Class Schedule").Select
Rows("5:5").Select
Selection.Delete Shift:=xlUp