Copy original worksheet when opening a new tab

[DJKempo]

Hello,
I am trying to automate a training sign off process and was looking for help to solve my issue.
I would like to open a 'blank' training sign off sheet, using drop downs to fill in an employees details, and have an area for someone to sign it.
What I am look for is a way to then open the original 'blank' sign off sheet when I click on '+' to open a new worksheet, so then then I can fill in different employees' details.

Any help would be great!

 

[ExcelToDAX]

This link should be able to guide you!

How to set a default template in Excel

Excel has the ability to use a custom template for all new workbooks. If you find yourself making the same changes to new workbooks (changing row height, font size, zoom, etc.) a default custom template can save you time and trouble.

exceljet.net

 

[HighAndWilder]

Hello,
I am trying to automate a training sign off process and was looking for help to solve my issue.
I would like to open a 'blank' training sign off sheet, using drop downs to fill in an employees details, and have an area for someone to sign it.
What I am look for is a way to then open the original 'blank' sign off sheet when I click on '+' to open a new worksheet, so then then I can fill in different employees' details.

Any help would be great!

Welcome to Mr Excel.

Put this code in the Workbook code module. See attached image for help.

How are the new sheets to be named? Do you have a naming convention?
This convention will need to be implemented before this code can be used. Just let me know,

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim strNewSheet As String

  If MsgBox("Create a new Training Sign Off Sheet?", vbYesNo, "Question.") = vbYes Then

    strNewSheet = ActiveSheet.Name
    
    ' Substitute "BlankSignOffSheet" with the name of the template sheet.
    Worksheets("BlankSignOffSheet").Copy after:=Worksheets(strNewSheet)
    
    ActiveSheet.Name = "SignOffSheet1"
    
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets(strNewSheet).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
  
  End If
  
End Sub

 

[DJKempo]

This link should be able to guide you!

How to set a default template in Excel

Excel has the ability to use a custom template for all new workbooks. If you find yourself making the same changes to new workbooks (changing row height, font size, zoom, etc.) a default custom template can save you time and trouble.

exceljet.net

Thank you. I will look at this.

 

[DJKempo]

Thanks. I was hoping the employees name that is selected in the drop-down menu could be used as the naming convention?

 

[HighAndWilder]

Thank you. I will look at this.

Thanks. I was hoping the employees name that is selected in the drop-down menu could be used as the naming convention?

But isn't that drop down on the copied new sheet?

Do you only expect to ever have one sheet for each person or people with a particular name?

 

[DJKempo]

Ideally, the end result I am looking for is a way to open a blank form and fill it out with drop downs, then once the form is completed the employee's name in the drop down is used to name the tab, and a new tab opens with a blank form.
Hope that makes sense?

 

[HighAndWilder]

Ideally, the end result I am looking for is a way to open a blank form and fill it out with drop downs, then once the form is completed the employee's name in the drop down is used to name the tab, and a new tab opens with a blank form.
Hope that makes sense?

That is possible.

The sheet can be renamed as soon as the persons name is selected.

What cell is the drop down list in?

 

[DJKempo]

I have not made the form yet, so it can be any at the moment. I know to manipulate code, just not how to begin form scratch.

 

[HighAndWilder]

I have not made the form yet, so it can be any at the moment. I know to manipulate code, just not how to begin form scratch.

So :

Set up a worksheet called "BlankSignOffSheet". You can change this later.

Copy the procedures below into the worksheet code module and the workbook code module as indicated.

Click on the + to create a new sheet.

Change the entry in cell E9 in yellow in the new sheet and the worksheet name will change.

Worksheet Code Module.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Err_Handler

  If Target = Range("E9") Then
  
    ActiveSheet.Name = Target.Value
  
  End If
  
Exit_Handler:

  Exit Sub

Err_Handler:

  MsgBox "There has been an error renaming this worksheet", vbOKOnly, "Warning"

  Resume Exit_Handler
  
End Sub

Workbook Code Module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim strNewSheet As String

  If MsgBox("Create a new Training Sign Off Sheet?", vbYesNo, "Question.") = vbYes Then

    strNewSheet = Sh.Name
        
    ' Substitute "BlankSignOffSheet" with the name of the template sheet.
    Worksheets("BlankSignOffSheet").Copy after:=Worksheets(strNewSheet)
    
    ActiveSheet.Name = "TemporarySheetName"
    
  End If
  
  Application.DisplayAlerts = False
  On Error Resume Next
  Sh.Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
    
End Sub