Insert sheet with macro

[Almondia]

Hi,

I want a macro that:

1. Inserts a new sheet based on a template (not an empty sheet)
2. A box asking for the new sheet name I want to use, eg. ABCD
3. If the sheet name already is used, it should count the sheets, eg. ABCD(1), ABCD(2) and so on.


I have tried to combine information from several examples into this:

Sub Insert_Sheet()
    Dim sh As Worksheet
    Dim shName As String

    'name of the sheet template
    shName = "J:\copy_sheet_mall.xltm"

    'Insert sheet template
    With ThisWorkbook
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
                            after:=.Sheets(.Sheets.Count))
    End With

    On Error Resume Next
     sh.Name = InputBox("Enter sheet name")
         If Err.Number > 0 Then
        MsgBox "Sheet name: " & sh.Name & " alredy in use."
        MsgBox "Sheet renamed to: " & sh.Name & Sheets.Count & ""
         Err.Clear
    End If
    On Error GoTo 0
End Sub

Problem 1:
It seems like the template file must be in the Users\Appdata..... folder. Not possible with Network Access to J:\. Then it's not possible for multiple users to insert sheets based on the template. Any solution?
Maybe an empty template within this files that it copies, instead of adding a new from a separate file?

Problem 2:
If the sheet name ABCD is being used, it renames it to Sheet1(2), Sheet1(3) and so on, instead of ABCD(2). How to solve this?

Problem 3:
Not the biggest issue, but it doesn't refer to the correct sheet names in the message boxes.

[AlphaFrog]

Try this...

Sub Insert_Sheet()
        
    Dim strName As String
    Dim i As Long
    Const shTemplate As String = "J:\copy_sheet_mall.xltm" 'Path and file name of the sheet template
    
    strName = Application.InputBox("Enter sheet name", "Sheet Name", Type:=2)
    If strName = "False" Then Exit Sub  'User canceled
    
    If Evaluate("ISREF('" & strName & "'!A1)") Then  'Test if worksheet name exists
        i = 2
        Do Until Not Evaluate("ISREF('" & strName & "(" & i & ")'!A1)")
            i = i + 1
        Loop
        MsgBox "Sheet name: " & strName & " alredy in use." & vbLf & vbLf & _
               "Sheet renamed to: " & strName & "(" & i & ")", _
               vbInformation, "Sheet Name Exists"
        strName = strName & "(" & i & ")"
    End If
    
    'Insert sheet template
    Sheets.Add(Type:=shTemplate, After:=Sheets(Sheets.Count)).Name = strName
    
End Sub