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.
Bookmarks