Mervin Knight
New Member
Ok...
I have a macro that copies a (self created) hidden template sheet, and moves the copy to the end of the worksheets. Then it asks the user to name the new sheet using an Inputbox. Now, if it's given an existing/duplicate name I want a MsgBox to state "A sheet with that name already exists - please use another name" and offer the InputBox again.
AND also the problem is... if there is another copy of the template other than "Template (2)" like Template (3) or Template (4) it only renames Template (2) not Template (3) or Template (4)
how can avoid there being more than one template ?
Here is the code I have so far... ANY HELP WILL BE APPRECIATED!
To illustrate this a worksheet called "Template" is hidden and any amount of sheets can be after that.
Have attached a sample
I have a macro that copies a (self created) hidden template sheet, and moves the copy to the end of the worksheets. Then it asks the user to name the new sheet using an Inputbox. Now, if it's given an existing/duplicate name I want a MsgBox to state "A sheet with that name already exists - please use another name" and offer the InputBox again.
AND also the problem is... if there is another copy of the template other than "Template (2)" like Template (3) or Template (4) it only renames Template (2) not Template (3) or Template (4)
how can avoid there being more than one template ?
Here is the code I have so far... ANY HELP WILL BE APPRECIATED!
To illustrate this a worksheet called "Template" is hidden and any amount of sheets can be after that.
Have attached a sample
Code:
Sub Add_new_Page()
'
' Add_new_Page Macro
' Unhides the Template page, copies, moves it to the end and asks to rename it
'
'
Sheets("Template").Visible = True
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets("Template (2)").Select
Call Sheet_Nm
Sheets("Template").Visible = False
End Sub
Sub Sheet_Nm()
response = InputBox("Name of the Sheet?", vbOKCancel)
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(response) Then
MsgBox "This sheet already exists - please use another name"
'If response = False Or response = "" Then
'MsgBox "Invalid Name"
Exit Sub
Else
ActiveSheet.Name = response
End If
Next
Sheets(ActiveSheet.Name).Name = response
End Sub