I've spent some time looking for answers online and can't seem to find a resolution to a particular problem.
I have a spreadsheet and macro that takes info. from a master schedule and creates new tabs for each participant with their specific schedule listed. I execute this by having a "Template" tab which contains a generic form letter than can be individualized for each participant on the master schedule tab (which is already formatted for printing). If you input the participants name in M3, the form will populate with their schedule info.
Currently, the code is looped to create a new tab for the participant in the top row of the master schedule, copy/paste the template form in the new sheet, and then generate the schedule for that student. Unfortunately, the formatting won't copy over:
Do
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
Loop Until IsEmpty(ivalue) = True
I believe what would work is to: create the newsht; generate the individual schedule on "Template"; copy/paste the schedule to newsht (so that formatting carries over); then copy/paste special (values) the individual schedule on newsht to get rid of the links to the master schedule; erase the name in "Template"; delete row 5 and start with the next row. Unfortunately, I don't know how to write that into the code!
I appreciate any help and insight into this issue.
Bookmarks