Create/Rename new sheet from hidden template

[CSS2018]

Hi there,

I have a time sheet excel file with a Project Data list of all the employees who could submit a time sheet. I have made a macro button to copy a hidden sheet named COPYME and rename it using an InputBox which works if you follow through with with the input box.

Things I need help with:

1. When the user hits cancel on the input box, it creates a new sheet called COPYME (2). I want to change this so that if they hit cancel nothing happens.

2. If the user enters a name that is already there it gives an error. I want to change this so it gives an error message saying "Employee sheet already exists" and nothing happens.

Sub CopySheet()
   Dim MySheetName As String
   
   'MySheetName = ActiveCell.Text
   'OR
   MySheetName = InputBox("Enter a Sheet Name!")
   
   Application.ScreenUpdating = False


    Sheets("COPYME").Visible = True
    Sheets("COPYME").Select
    Sheets("COPYME").Copy Before:=Sheets(4)
    Sheets("COPYME").Visible = False
  


   If MySheetName = "" Then
      MsgBox "No sheet name was entered, ending!"
      Exit Sub
   Else
      If ValidSheetName(MySheetName) Then
         Sheets("COPYME").Copy After:=Sheets("PROJECT DATA")
         ActiveSheet.Name = MySheetName
      Else
         MsgBox "There is an invalid character in the sheet name!"
      End If
   End If
   
End Sub

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim MySheetName As String
    MySheetName = InputBox("Enter a Sheet Name!")
    If MySheetName = "" Then
        MsgBox "No sheet name was entered, ending!"
        Exit Sub
    Else
        On Error Resume Next
        Set ws = Sheets(MySheetName)
        If Err.Number <> 0 Then
            Sheets("COPYME").Visible = True
            Sheets("COPYME").Copy After:=Sheets("PROJECT DATA")
            ActiveSheet.Name = MySheetName
            Sheets("COPYME").Visible = False
        Else
            MsgBox "Worksheet " & MySheetName & " already exists."
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[CSS2018]

My 2 needs were perfectly met and it works. Thank you!!

 

[mumps]

You are very welcome.