LinkBack URL
About LinkBacksHi,
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