Upon worksheet creation copy hidden-sheet "TEMPLATE"

1
votes

Using Excel 2013 macros I'd like to be able to, upon worksheet creation (the "+" sign or right click, new worksheet), to instead of creating a new worksheet, copy a hidden "TEMPLATE" worksheet instead to use as a template for this workbook. There will be many worksheets to be created initially and over time, this workbook will be used every day with potentially other workbooks open at the same time as well.

The code I already have asks for the user to input the name of the worksheet upon creation and calls to sort the current workbook's worksheets alphanumerically and rebuild the TOC. Is there any way to change the current code to match it's new purpose? NOTE: This code is in ThisWorkbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Dim sName As String
    Dim bValidName As Boolean
    Dim i As Long

    bValidName = False

    Do While bValidName = False
        sName = InputBox("Please name this new worksheet:", "New Sheet Name", Sh.Name)
            If Len(sName) > 0 Then
            For i = 1 To 7
                sName = Replace(sName, Mid(":\/?*[]", i, 1), " ")
            Next i
            sName = Trim(Left(WorksheetFunction.Trim(sName), 31))
            If Not Evaluate("ISREF('" & sName & "'!A1)") Then bValidName = True
        End If
    Loop

    Sh.Name = sName

    Call Sort_Active_Book
    Call Rebuild_TOC

End Sub

Edit 1: Note: The "TEMPLATE" worksheet only pertains to this workbook, does not need to be used in another workbook, and is a hidden worksheet in this workbook.

2
vbaexcel
Call shtTemplate.Copy() and Sh.Delete()? – GSerg

2 Answers

2
votes

Updated code. GSerg has it right:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Dim wb as Workbook
    Dim wsTemp as Worksheet
    Dim sName As String
    Dim bValidName As Boolean
    Dim i As Long

    bValidName = False

    Do While bValidName = False
        sName = InputBox("Please name this new worksheet:", "New Sheet Name", Sh.Name)
            If Len(sName) > 0 Then
            For i = 1 To 7
                sName = Replace(sName, Mid(":\/?*[]", i, 1), " ")
            Next i
            sName = Trim(Left(WorksheetFunction.Trim(sName), 31))
            If Not Evaluate("ISREF('" & sName & "'!A1)") Then bValidName = True
        End If
    Loop

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    Set wb = ThisWorkbook
    Set wsTemp = wb.Sheets("TEMPLATE")
    wsTemp.Visible = xlSheetVisible
    wsTemp.Copy After:=wb.Sheets(wb.Sheets.Count)
    ActiveSheet.Name = sName
    Sh.Delete
    wsTemp.Visible = xlSheetHidden   'Or xlSheetVeryHidden

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With

    Call Sort_Active_Book
    Call Rebuild_TOC

End Sub
1
votes

Is your template saved a location you could pull from for anyone who needs it? If not you will just have to create a macro to format a template.

If you have a template ready, you just need the full path of that file. I would turn off application.screenupdating = false and open that file, copy the sheet you want and paste it to your current doc, then close the template file and application.screenupdating = true.

Edit:

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Template").Visible = True
sheets("Template").copy after:=Sheets(1)
Sheets("Template").Visible = False
ActiveSheet.Name = sName
Sheets(Sh.Name).Delete

Application.ScreenUpdating = True
Application.DisplayAlerts = True

This will work, you will just need to change the template path