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