| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
Thanks David,
I'll try as you have suggested. And get back to the thread when done. Cheers J_J "David" wrote in message ... 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to sum special to new sheets.
Yes...it works like a charm. Thanks a million.
Regards J_J "David" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Special Pasting a work book with many sheets and formulas | Excel Discussion (Misc queries) | |||
| I need help with creating a special macro . | Excel Worksheet Functions | |||
| Special macro | Excel Programming | |||
| Copy information from other sheets to one special sheet. | Excel Programming | |||
| Macro to Paste Special | Excel Programming | |||
![J_J[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
