Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim isect As Range
' If more than one cell updated at once, exit sub
If Target.Count > 1 Then Exit Sub
' Check to see if update made in range A11:G23
Set myRange = Range("A11:G23")
Set isect = Intersect(Target, myRange)
If isect Is Nothing Then Exit Sub
' Count to see how many cells in range match entry
If Application.WorksheetFunction.CountIf(myRange, Target) = 1 Then
Cells.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Name = Target.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newSh As Worksheet
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
If Application.CountIf(Range("A11:G23"), Target.Value) = 1 Then
Set newSh = Sheets.Add
newSh.Name = Target.Value
End If
End Sub
They way that the code is written, they could enter something outside of range A11:G23, and if it matches exactly one item with range A11:A23, then it will create a new sheet.I need a bit of code that when any text is entered into range A11:G23 if that specific text isnt matched in the range anywhere it creates a new sheet (a copy of sheet2) and names it with the text entered.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim isect As Range
' If more than one cell updated at once, exit sub
If Target.Count > 1 Then Exit Sub
' Check to see if update made in range A11:G23
Set myRange = Range("A11:G23")
Set isect = Intersect(Target, myRange)
If isect Is Nothing Then Exit Sub
' Count to see how many cells in range match entry
If Application.WorksheetFunction.CountIf(myRange, Target) = 1 Then
[COLOR=#ff0000] Sheets("Sheet2").Activate[/COLOR]
Cells.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Name = Target.Value
End If
End Sub
Yep, you're right, Joe4. Senility strikes again!JLGWhiz,
I see one potential problem with your code.
They way that the code is written, they could enter something outside of range A11:G23, and if it matches exactly one item with range A11:A23, then it will create a new sheet.
You code probably needs to be amended to make sure that the cell being updated (Target) resides in the range A11:G23 (which is why I used Intersect).
We can't beat Mother Nature. She always wins in the end.Yep, you're right, Joe4. Senility strikes again!
Trying to capture text that is no longer there is a bit tricky (see: How do I get the old value of a changed cell in Excel VBA? - Stack Overflow). I prefer to do it another way.But I realise now that I will need the new sheet to be deleted if the specific text is deleted and doesn't appear anywhere else in the range. I imagine this is not so easy.