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.