macro to sum special to new sheets.

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

Hi,
Started on your problem and this is what I have so far:
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
Stop
End Sub
Then I was working on a formula, but I am getting some strange results from
the formula, maybe someone can help with the formula. It is an array formula:
{=SUM(IF(Sheet1!$A$1:$A$30=$A$2,IF(Sheet1!$B$1:$G$1=Stud1!B1,Sheet1!$B$2:$G$30,0),0))}
What this is pulling in is incorrect and I can not see why. It is pulling in
the result below the Row with the correct answer. The way the sheet is set
up, it should be looking at Lesson and the Student, but for some reason it is
bringing back the Row below the correct student. I could not get the formula
to span multiple worksheets, but I figured I might find a way to work around
that later.

It might be possible to do this with Offset. One of the MVPs may come up
with an easier method too.

Thanks,

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

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

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

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

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

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