LinkBack URL
About LinkBacks
Guest 
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
> > >>
> > >>
> > >>
> >
> >
> >
Register To Reply
Bookmarks