How to create worksheet template - NOT workbook template

Hi,

I have a workbook containing multiple worksheets attached. At the end of each month I want to create a new worksheet using a template so that formulas etc are transferred to a new sheet but all other other sheets remain the same, the idea being that I can add 'June', 'July' etc as time goes by. Saving as a .xlt in the templates folder is not what I'm after as this opens up a whole workbook, overwriting the other tabs. Ideally I want to right click a tab at the bottom, select 'Insert' and open up a template, rename it for the appropriate month, without this effecting the other tabs in any way.

How can I do this?

Thanks,

Will

Hi again RoyUK,

I am not understanding how to get that to work in my workbook, apologies but you're talking to someone who isn't savvy at all with codes. How do I get their summary sheet code below to work and become relevant for my workbook?

'---------------------------------------------------------------------------------------
' Module    : Sheet3
' DateTime  : 08/02/2007 08:08
' Author    : Roy Cox (royUK)
' Website   :  more examples
' Purpose   : Add a sheet based on a hidden template sheet into a specific position in the workbook
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.
'---------------------------------------------------------------------------------------
Option Explicit

Private Sub cmbAdd_New_Sheet_Click()
    Dim iWsCnt As Integer
    iWsCnt = ThisWorkbook.Worksheets.Count
    'hide operation from user
    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("Template")
        .Visible = xlSheetVisible
        '        Call MsgBox("Click Yes to position the new sheet immediately after Top. Click No to place the new sheet before Bottom.", vbQuestion, "Add a sheet")
        Select Case MsgBox("Click Yes to position the new sheet immediately after Top." & vbNewLine & vbNewLine & "Click No to place the new sheet before Bottom.", vbYesNo Or vbQuestion Or vbDefaultButton1, "Add extra sheet")

            Case vbYes
                ' add a sheet after Top
              .Copy before:=Worksheets(5)
            Case vbNo
                'add a sheet before Bottom
                .Copy after:=Worksheets(iWsCnt - 1)
        End Select
        'name the new sheet
        ActiveSheet.Name = "Page" & iWsCnt - 4    'you may need to change 5 according to the number of sheets outside the data sheets
        .Visible = xlSheetVeryHidden
    End With

    Application.ScreenUpdating = True

End Sub
Sub Macro1()

        Dim sh As Worksheet
        Dim shName As String
        Dim n As Integer

        'Name of the sheet template
        shName = "Sheet.xlt"

        'Insert sheet template
        With ThisWorkbook
            Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _ After:=.Sheets(.Sheets.Count))
        End With

    End Sub

i.e.
Where and how do I save my template? (I don't need a mssg box to appear either), is it saved into the normal template folder or somewhere else?
How do I insert the new sheet once it's saved?

- Will