hi,
If you are getting an error, I expect it is because you have the code in the
wrong place ie in a Sheet module. It needs to be in a regular Module. If you
can get it in the right place, try it again.

Thanks,



"David" wrote:

Hi,
I will take a look at it tomorrow. It worked for me. It may be you have your
sheets layed out differantly than I hve mine laid out.

Name Les1 Les2 Les3 Les4 Les5 Les6
Stud1 60 60 60 60 60 60
Stud2 61 61 61 61 61 61
Stud3 62 62 62 62 62 62
Stud4 63 63 63 63 63 63
.
.
.
You would start on the cell that says "Stud1" and this would be "Sheet1".

Thanks,

"J_J" wrote:

Thank you for your efforts David,
It didn't work for me. it gives an error (method) even at the start of
copying titles...
I guess there should be an easier way of solving this. And hope that experts
from this NG will help.
Sincerely
J_J

"David" wrote in message
...
Hi Again,

This may work better, but I have not been able to test it completely. You
start on the students name you want a summary sheet for on sheet1, it
should
iterate trrough 10 worksheets named Sheet1, Sheet2, etc and give you a
total
on a sheet with the students name as the sheet name.

Sub Macro2()
NewSheetName = ActiveCell.Value
Sheets("Sheet1").Select
Sheets.Add
ActiveSheet.Name = NewSheetName
Sheets("Sheet1").Select
Range("B1:G1").Select
Selection.Copy
Sheets(NewSheetName).Select
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2").Value = NewSheetName
Range("B2").Select
Dim ws As Worksheet
For i = 1 To 10
Sheets("Sheet" & i).Select
Range("A1").Select
Cells.Find(What:=(NewSheetName), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
False, SearchFormat:=False).Activate
Les1 = Les1 + ActiveCell.Offset(0, 1).Value
Les2 = Les2 + ActiveCell.Offset(0, 2).Value
Les3 = Les3 + ActiveCell.Offset(0, 3).Value
Les4 = Les4 + ActiveCell.Offset(0, 4).Value
Les5 = Les5 + ActiveCell.Offset(0, 5).Value
Les6 = Les6 + ActiveCell.Offset(0, 6).Value
Next
Sheets(NewSheetName).Select
Range("A2").Select
ActiveCell.Offset(0, 1).Value = Les1
ActiveCell.Offset(0, 2).Value = Les2
ActiveCell.Offset(0, 3).Value = Les3
ActiveCell.Offset(0, 4).Value = Les4
ActiveCell.Offset(0, 5).Value = Les5
ActiveCell.Offset(0, 6).Value = Les6
End Sub



"J_J" wrote:

Hi,
I have a workbook with 10 worksheets. In column A2:A30, I have "Student
Names". I have "Lesson Names" in Range B1:G1 which is constant and
doesn't
change from worksheet to worksheet. But range B2:G30 has different
integer
values in different worksheets.
Now I need a macro, that when executed will create new worksheets with
the
unique Student Names from columns A:A in all sheets, and sum up all
values
from all worksheets for particular Lesson Grades from range B2:G30.
To simplify,
Say I have a name "Jack Junior" in A5 for Sheet1. The same name may
appear
in different cell positions for column A in Sheet2, Sheet3, ...Sheet10.
Now
when I execute the macro, I need my macro to create a new sheet named
"Jack
Junior", with the same lesson names from all sheets for B1:G1, and if B1
displays say "Maths", cell B2 will be the sum of all Maths Grade values
for
"Jack Junior" displayed in Sheet1, Sheet2, Sheet3, ...Sheet10.
Can macro experts give example solutions?
Regards
J_J