• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying Sheets

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

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
 

Attachments

  • ROY.xlsm
    29 KB · Views: 3
Hi Mervin, and welcome to the forum! :awesome:

I would suggest changing the code within Module2 to this:
Code:
Sub Add_new_Page()
'
' Add_new_Page Macro
' Unhides the Template page, copies, moves it to the end and renames it "Your New Page"
'
   
Dim wsTemp As Worksheet
Dim wsNew As Worksheet
Dim wsTest As Worksheet
Dim strName As String

Set wsTemp = ThisWorkbook.Worksheets("Template")

newName:
Do
    strName = InputBox("Name of the Sheet?", "Sheet Name", strName)
   
    'Test if sheet already exists
    Set wsTest = Nothing
    On Error Resume Next
    Set wsTest = ThisWorkbook.Worksheets(strName)
    On Error GoTo 0
   
    If Not wsTest Is Nothing Then
        MsgBox "This sheet already exists!", vbOKOnly, "Error"
    End If
Loop Until wsTest Is Nothing

If strName = "" Then
    'User cancelled
    Exit Sub
ElseIf InStr(1, strName, "template", vbTextCompare) Then
    MsgBox "Can't have the word 'template' in name"
    GoTo newName
End If

'Now that we have a good name, proceed w/ copy
Application.ScreenUpdating = False
wsTemp.Visible = True
wsTemp.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = strName
wsTemp.Visible = False
Application.ScreenUpdating = True
End Sub
No need to have the 2nd macro. Is this what you meant?
 
See attached. It will not commit name until valid name which is not duplicate is entered into input box.

Edit: woops, should have refreshed before posting ;)
 

Attachments

  • ROY.xlsm
    29 KB · Views: 6
See attached. It will not commit name until valid name which is not duplicate is entered into input box.

Edit: woops, should have refreshed before posting ;)

Thanks Chihiro... you actually achieved what I set out to achieve...whether it is the correct way - it worked! Short sharp and straight to the point - Cheers!
 
Mr. Chihiro
Thanks a lot for great help
When I tested your file and typed an existing sheet for the first time , there is another inputbox to appear and typed the same existing sheet for a second time I encountered an error.
Another point we can check sheet existence without looping
Please try this code
Code:
Sub Add_New_Sheet()
'Unhides The Template Page, Copies, Moves It To End And Renames It "Your New Page"
'----------------------------------------------------------------------------------
    Dim Response As String

    Application.ScreenUpdating = False
        Response = InputBox("Name Of The Sheet?", vbOKCancel)
   
Again:
        Select Case True
            Case StrPtr(Response) = 0, Len(Response) = 0
                Exit Sub
            Case Else
                If Evaluate("=ISREF('" & Response & "'!A1)") Then
                    Response = InputBox("Name Already Exists, Enter Another Name", vbOKCancel): GoTo Again
                Else
                    Sheets("Template").Visible = True
                    Sheets("Template").Copy After:=Sheets(Sheets.Count)
                End If
        End Select
   
        ActiveSheet.Name = Response
        Sheets("Template").Visible = False
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Back
Top