VBA to copy template sheet and rename it along with if name deleted corresponding sheet will be deleted.

[BSHANEH]

Can someone help me out? I know very little about VBA code. I'm currently working on a diver profile log that I would like to be able to enter the name of the person on the master sheet and it copy and rename the template sheet to that name as well as entering that name in cell A1 in the copy of the template.I would like it to put a message up if I enter a duplicate name. If I delete that name, I would like to have it delete the corresponding sheet as well. If possible, I would also like to be able to click on any name on the master sheet and it bring up that sheet. I've tried a few different code's and none seem to work like I want. I appreciate any help.

 

[bebo021999]

Assume there are at least two sheets at begginning: "Master" and "Template"
Try below code. it must be placed in worksheet "Master" module (right click on tab "Master"/view code)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim lr&, i&, c&, cell As Range, nameS(1 To 100000, 1 To 2)
Dim ws As Worksheet, temp As Worksheet
If Target.Column = 1 And Target.Row > 2 Then
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set temp = Worksheets("Template")
For Each cell In Target
    If Not IsEmpty(cell) And WorksheetFunction.CountIf(Range("A3:A" & lr), cell) > 1 Then
        MsgBox "Dupplicate name. Try again"
        Target.ClearContents
        Exit Sub
    End If
Next
    For Each ws In Sheets
        If ws.name <> "Master" And ws.name <> "Template" Then
            If lr = 2 Then
                ws.Delete
            Else
                i = i + 1
                nameS(i, 1) = ws.name
            End If
        End If
    Next
    For Each cell In Range("A3:A" & lr)
        If lr = 2 Then Exit Sub
        If Not IsEmpty(cell) Then
            For Each ws In Sheets
                If ws.name <> "Master" And ws.name <> "Template" And ws.name = cell.Value Then GoTo z:
            Next
            temp.Copy after:=Sheets(Sheets.Count)
            ActiveSheet.name = cell.Value
            ActiveSheet.Range("A1").Value = cell.Value
            Worksheets("Master").Activate
        End If
z:
    Next
    For Each ws In Sheets
        If ws.name <> "Master" And ws.name <> "Template" And _
        WorksheetFunction.CountIf(Range("A3:A" & lr), ws.name) = 0 Then ws.Delete
    Next
End If
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Target) Or Target.Count > 1 Or Target.Row < 3 Or Target.Column <> 1 Then Exit Sub
Worksheets(Target.Value).Activate
End Sub

 

[BSHANEH]

This worked perfectly can't thank you enough for the help.