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" <David@discussions.microsoft.com> wrote in message
news:6B53E70E-ADD7-42F5-BE61-379BEB9E3F6A@microsoft.com...
> 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
>>
>>
>>