Thanks David,
I'll try as you have suggested. And get back to the thread when done.
Cheers
J_J

"David" <David@discussions.microsoft.com> wrote in message
news:0361C5C4-7B32-4F82-8435-60AEE2073264@microsoft.com...
> 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" <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
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >