Creating new sheet based on user input - old sheets get deleted by mistake

Hi Greg,

Really appreciate the response! Thanks

I've attached an example copy of the spreadsheet I'm working on. There's two template versions, one I've hidden the template for and removed the visible = xlSheetHidden lines (Template 1) and the other I haven't (Template 2). I hope this helps clarify what is/is not happening. How would I be able to reference the newly created sheet within the code? Ideally I would like the template sheets to be hidden, but not sure if this is possible without using sheet indexing.

Hi again,

Unfortunately there are several basic errors in the code you posted originally, e.g.:

Sub AdditionalItems()
  
  Dim newName As String
  Dim ts As Worksheet
  Dim ws As Worksheet
  Dim wb As Workbook
  Dim nss As Worksheet
  Dim lastow As Long
  Set wb = ThisWorkbook
  
  With wb
Application.ScreenUpdating = False

    Set ws = Sheets("DATABASE")
    Set ts = Sheets("STUDY TEMPLATE 1")
    lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ts.Visible = xlSheetVisible 'shows template - can only create new sheets if template is visible

  On Error Resume Next
  
  newName = InputBox("Enter the name for the copied worksheet", "New Project") 'user input for name of new sheet
    ts.Visible = xlSheetHidden
  If newName <> "" Then
    ts.Copy After:=Worksheets(Sheets.Count)
    ts.Visible = xlSheetHidden
    
    On Error Resume Next
    
    Set nss = ActiveSheet
    nss.Name = newName 'changes name to user input
    nss.Range("B11").Value = newName 'adds name to newly created sheet
    
    ws.Cells(lastrow, "A").Value = newName 'adds newly created sheet to database
    ns.Range("B11").Clear

    ts.Visible = xlSheetHidden
    Application.ScreenUpdating = True
       
  End If
  End With
End Sub

The compiler would have identified these errors if you had placed "Option Explicit" as the first line in the CodeModule - you should ALWAYS do this.

Also, you should not include open-ended "On Error Resume Next" statements - these can produce VERY strange/dangerous results! If you need to use them, you should always restore error handling as soon as possible by means of an "On Error GoTo 0" statement.


See if the attached version of your workbook does what you need - it uses the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Sub AdditionalItems()

    Call CreateNewWorksheet(sSourceSheetName:="STUDY TEMPLATE 1")

End Sub


'=========================================================================================
'=========================================================================================


Sub PerPatient_Click()
 
    Call CreateNewWorksheet(sSourceSheetName:="STUDY TEMPLATE 2")

End Sub


'=========================================================================================
'=========================================================================================


Sub CreateNewWorksheet(sSourceSheetName As String)
  
    Dim wksTemplate     As Worksheet
    Dim wksDatabase     As Worksheet
    Dim lLastRowNo      As Long
    Dim sNewName        As String
    Dim wksNew          As Worksheet

    sNewName = InputBox("Enter the name for the copied worksheet", "New Project")

    If sNewName <> "" Then

        Set wksDatabase = Sheets("DATABASE")
        Set wksTemplate = Sheets(sSourceSheetName)

        With wksDatabase
            lLastRowNo = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End With

        Application.ScreenUpdating = False

            wksTemplate.Copy After:=Worksheets(Worksheets.Count)

            Set wksNew = Worksheets(Worksheets.Count)

            With wksNew

                .Visible = xlSheetVisible
                .Name = sNewName
                .Range("B11").Value = sNewName
                .Activate

            End With

            wksDatabase.Cells(lLastRowNo, "A").Value = sNewName

        Application.ScreenUpdating = True

  End If

End Sub

Worksheets CAN be copied (as hidden worksheets) when their Visible property is set to "Hidden" - it's only when that property is set to "VeryHidden" that they can't be copied.


Hope this helps - please let me know how you get on.

Regards,

Greg M