macro to sum special to new sheets.

Hope it works

"J_J" wrote:

> 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
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

Yes...it works like a charm. Thanks a million.
Regards
J_J

"David" <David@discussions.microsoft.com> wrote in message
news:EB5E61BE-D96C-409F-85D4-2050E6E2ED4A@microsoft.com...
> Hope it works
>
> "J_J" wrote:
>
>> 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
>> >> > >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >> >
>>
>>
>>