Hello,
I have 2 problems with a specific macro I created. The macro creates a new worksheet that is a copy of my template sheet and renames it to the value in the original cell on my "Masterlist". My problem is I only want this to happen when a new item is entered in range("b11:b"). Right now it happens when you enter new text in any cell in the "Masterlist" sheet.
My second issue is when I delete an entry on the "Masterlist" it also creates a new copy and then renames it. How could I alter my code to avoid this.
Thank you for any help.
Dave
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim wsNew As Worksheet
Dim MySheetName As String
If Target.Cells.Count > 1 Then Exit Sub
MySheetName = Target.Value
Sheets("Template").Visible = True
On Error Resume Next
If Not Intersect(Target, Range("b11:b")) Is Nothing Then
Set wsNew = Sheets(Target.Text)
If wsNew Is Nothing Then Sheets("Template").Copy After:=Sheets("Masterlist")
ActiveSheet.Name = MySheetName
Sheets("Template").Visible = False
End If
Application.ScreenUpdating = True
End Sub
Bookmarks