I have taken the liberty of sending a sample file to you via email.

As I mention in the email, the total solution is a bit complex to describe
in a post here. Basically it involves setting up a list with all student
names in it, giving that list a Name (making it a named range), then using
that named range as the source list for Data Validation in a cell on the
"summary" sheet so that only one summary sheet is required to deal with all
students for printout and presenting to them.

It also involves setting up several formulas using RANK(), AVERAGE(),
VLOOKUP(), and a combined INDEX and MATCH formula to achieve the results I
think you're looking for.

" wrote:

Hello,
I am trying to design an excel workbook for my class that will
automatically copy scores from various subjects(8 subt.)and paste them
in another. Let me give an example.
There are 8 subjects and 40 students in a class. Each subject sheet
has the following headings
NAME, HOME WORK, PROJECT WORK, EXAMS, TOTAL, POSITION IN SUBT., GRADE.
Now after preparing the above sheet, I want to take say student 1's
name, total, position and grade and paste it automatically in another
wksheet or book. This will be done for all the 8 subjects and the avg
calculated. Each student in the class will receive such a report which
will be printed out (the priting is not authomatic anyway).
So after filling all the score sheets for the eight (8) subjects,
the student's personal report will automatically be ready.
I'm new to VBA but any help in VBA, simple Excel Macros or any form
will help.
thanks.

.