Mastersheet to Worksheet data copy - macros?

[DCT Mvumi School]

I'm out in Tanzania working for a school and I'm helping out with the school
accounts. Up until now there has been a lot of fraud so we are trying to
consolidate the information and put it all on excel or perhaps access to
ensure that we have an electronic record, as the cashier and accountant have
been using hard records and a lot of money has been going walk-about.

We have created an excel document with a master copy listing all 460
students on one worksheet. The categories are: FORM, FORM STREAM, SEX,
STUDENT TYPE, BOARDER OR DAY STUDENT, SPONSORED, CATEGORY OF SPONSORSHIP,
FIRST NAME, LAST NAME. We then have 1st Quarter Fees and whether or not they
are in credit or debit, going all the way to 4th Quarter. We also want to
have Pocket Money and Medical Expenses on the same Worksheet.

Here's the catch. We think the easiest way to do everything right now, is to
print off a page containg the student's NAME, FORM, B/D, SPONSORED?. And then
we would have two tables, one listing the School Fees details and another
listing Pocket Money and Medical Expenses.

Is there anyway of creating a macro or some sort of rule that states: Please
create 460 sheets from the mastercopy containing NAME, FORM, B/D, SPONSORED
and both tables?

Please help!!
Thank you

 

[Joel]

The code below expects the master sheet in the workbook to be called
"Summary" and ther is a 2nd worksheet called Template that will be copied for
each student. The code creates the worksheet name using the student last and
first name. I would sort the students by first anem and last name before
running macro so the worksheets are alphebitzed to make it easier to find a
stundent. There is no limit to the the number of worksheets that excel can
use except the more sheets you have the more memory you need on your PC.

Sub CreateSheets()

'Create a template sheet to be copied for each student
Set TemplateSht = Sheets("Template")

Set SummarySht = Sheets("Summary")
'skip header row on summary sheet
SumRowCount = 2

With SummarySht
Do While .Range("A" & SumRowCount) <> ""

LastName = .Range("A" & SumRowCount)
FirstName = .Range("B" & SumRowCount)
SPONSORED = .Range("C" & SumRowCount)
'add more data here that you want to copy from summmary sheet

'Add New sht by copying template
TemplateSht.Copy after:=Sheets(Sheets.Count)
Set NewSht = ActiveSheet
'change name or workshheet to match studnet name
NewSht.Name = LastName & "_" & FirstName

With NewSht
'add more code here to put data into new worksheet
.Range("B2") = SPONSORED
End With

SumRowCount = SumRowCount + 1
Loop

End With

End Sub