Macro the create new tab and paste data for template

[iuksob03]

I'm trying to create a macro that will create a new tab and fill the new tab with data existing in a hidden tab 'Template'A1:L50, which would include formulas. I would also like the tab to be named after the name of the person who's data would later be filled into the new tab. I assume once they hit the button to run the macro, it would have to prompt the user for an input before the name could be assigned to the tab?

 

[breynolds0431]

In the sheet Module for your template tab, add the following. This will update the sheet name whenever the field for the name is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim ResName as Range: set ResName = ws.range("C4") 'Update for actual cell with the resident's name

Dim kcell As Range: Set kcell = ws.Range("ResName")

If Not Application.Intersect(kcell, Range(Target.Address)) Is Nothing Then
         
          If kcell.Value <> "" Then
                    ws.name = kcell.Value
          End If

End If

End Sub

In a normal Module, add the following:

Sub createsheet()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim tWS As Worksheet: Set tWS = wb.Sheets("Template") 'Replace "Template" with your template's sheet name

tWS.Copy After:=wb.Sheets(wb.Sheets.Count) 'Will make a copy at the end of the sheet order
Dim nWS As Worksheet: Set nWS = ActiveSheet 'Declares the new copy sheet
nWS.name = "<Pending>" 'Adds a temporary sheet name

'Will allow a user to either add their name during sheet copy/creation or give info to complete later
Dim uName As String
Dim ResName as Range: set ResName = nWs.range("C4") 'Update for actual cell with the resident's name

uName = InputBox("If you'd like, you can enter your name now. Otherwise, " & _
          "leave blank and add to the sheet later.", "User Name", "")
         
          If uName = "" Then 'No name entered so do nothing
          Else
                  nWS.name = uName 'Sets sheet name with value typed in input box
                  ResName.Value = uName 'Sets worksheet space with name
          End If

End Sub

Then, you can set a button anywhere in your workbook to call the createsheet() procedure.

 

[iuksob03]

This works perfectly! Thank you