I think AppleScript probably is the best way to go here, but you probably ought to learn something about using it because it is often difficult to write and test a script on one system when it has to work on a different one. Yvan does a pretty good job of that, but my AppleScript skills are not that advanced.
My difficulties with your problem include:
- I don't know what changes you might have made to the stock template in Numbers
- I am using Snow Leopard, not Lion
- I believe UI scripting is needed to duplicate a sheet, and I don't own the tools to determine how to select a sheet (if that is possible).
So, working with the unmodified template on my system, this is a script that I think does what you need (but it would probably have to be edited to suit your situation):
(*
To run this script, UI scripting must be enabled. Numbers must be running with the Grade Book document open and the sheet "Reports" selected.
*)
tell application "Numbers"
activate
set theDoc to front document
set sourceTable to table "Student Grades" of sheet "Student Data" of theDoc
tell theDoc
repeat with i from 4 to ((row count of sourceTable) - 1)
set theName to value of cell i of column 1 of sourceTable
tell application "System Events"
tell process "Numbers"
click menu item "Duplicate" of menu 1 of menu bar item "Edit" of menu bar 1
end tell
end tell
set theName to value of cell i of column 1 of sourceTable
tell sheet (i - 2) of theDoc
set its name to (theName & " Report")
set value of cell 1 of row 3 of table "Student Lookup" to theName
end tell
end repeat
delete last sheet
end tell
end tell