If cell value less than X, a new sheet is created based on a template

[wong3]

Hi everyone,
I'm a teacher and I wanted to create notifications (new sheets based on a template) when a student scores less than 80%.

I have the following event code which will create a new sheet based on my template when a value is entered into a cell. I need two changes:
- Create a sheet when the value is less than 80 (instead of creating a sheet when a value is entered into the cell)
- Naming the sheet the student's name and the name of the test

If anyone can help. Thank you.

'Event code that runs if a cell value is changed
Private Sub Worksheet_Change(ByVal Target As Range)

'Check if the cell value in Column C is < 80
If Not Intersect(Target, Range("C:C")) Is Nothing Then

'Copy worksheet based on value in cell F2 in worksheet Sheet1 and put it last
Sheets(Worksheets("Sheet1").Range("F2").Value).Copy , Sheets(Sheets.Count)

'Rename worksheet to the value you entered.
ActiveSheet.Name = Target.Value

End If

'Go back to worksheet Sheet1
Worksheets("Sheet1").Activate

End Sub

Name	English Test	Math Test
Red	50​	100​		Template:	Template
Orange	100​	100​
Yellow	80​	90​
Green	90​	95​
Blue	88​	40​
Purple	92​	80​
Brown	20​	85​
Black	80​	92​
White	95​	50​

 

[Legacy 143009]

Hello!

What information would be inside the new sheet?

 

[bebo021999]

May be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WsName as string

'Check if the cell value in Column C is >= 80 or cell change not in column C then do nothing
If Intersect(Target, Range("C:C")) Is Nothing or target.value >=80 Then Exit Sub
WsName = Target,Offset(0,-2).value & "-" & cells(1,"C").value ' assume name of test is in row 1 ' for example: "Green - Math Test"

'Copy worksheet based on value in cell F2 in worksheet Sheet1 and put it last
Sheets(Range("F2").Value).Copy  After:=Sheets(Sheets.Count)

'Rename worksheet to the value you entered.
ActiveSheet.Name = WsName

'Go back to worksheet Sheet1
Worksheets("Sheet1").Activate
end sub

 

[wong3]

Hello!

What information would be inside the new sheet?

The new sheet would have their name (sourced from the table), a list of the tests and the grades (also sourced from the table), and some boiler plate language of how the student scored a non-passing grade and how to remediate (this would be a free text box).

 

[Legacy 143009]

The new sheet would have their name (sourced from the table), a list of the tests and the grades (also sourced from the table), and some boiler plate language of how the student scored a non-passing grade and how to remediate (this would be a free text box).

Basically, you want the copy of source sheet with student's name as tab name.