So the other day, a someone asked me how to automatically generate worksheets from cells.
Like they have an Excel sheet with kids, scores, and grades...
Worksheet called "master".
...they want to automagically generate a report card for each kid.
Worksheet callled "template".
This is a simplified example, we'll put the kids values in column C. I think you have to use VBA to create sheets and name sheets from cell values.
Open the code editor or make a new macro (which opens the code editor).
Make a new module under Microsoft Excel Objects.
This code loops thru the A column of master, makes a new sheet named after the kid based on the template, and plugs the row data into specified fields.
Sub generateReportCards()
' generates report cards for kids based on a master sheet of data
' and a template blank report card
Dim i As Integer ' need a variable to point to data keeping
i = 2 ' keeping up with iterator, skipping headings
Dim c As Range
For Each c In Range("A:A") 'loop thru all of A
If c.Value <> "" And c.Value <> "kids" Then 'skip blank and heading
Worksheets("template").Copy After:=Worksheets("template") ' copy template
Sheets("template (2)").Name = c.Value 'and name it after kid
Sheets(c.Value).Select
Range("C1").Select 'name ' all the copying
ActiveCell.FormulaR1C1 = c.Value
Range("C3").Select ' final grade
ActiveCell.FormulaR1C1 = Range("master!e" & i).Value
Range("B9").Select ' test1
ActiveCell.FormulaR1C1 = Range("master!b" & i).Value
Range("B10").Select ' test2
ActiveCell.FormulaR1C1 = Range("master!c" & i).Value
Range("B11").Select ' test3
ActiveCell.FormulaR1C1 = Range("master!d" & i).Value
i = i + 1 ' move the other pointer to keep up with iterator
End If
Next c
End Sub
Download the sample .xls file.
Obviously, you'll have to enable macros.
Office 2007 ->
1. Click on the Office button.
2. Click on Excel Options.
3. Check Show Developer tab in the Ribbon.
4. Click OK.
Office 2003 ->
- On the Tools menu, point to Macro, and then click Security.
- On the Trusted Publishers tab, select the Trust all installed add-ins and templates check box.
If this article was at least a little helpful, how about a +1?
