Changing existing macro to create and fill sheets based off a template

[jdev33]

This is what I'm using now:

Sub DepartmentName()
    Dim LCopyToRow As Long
    Dim LCopyToCol As Long
    Dim arrColsToCopy
    Dim c As Range, x As Integer
 
    On Error GoTo Err_Execute
 
 
    arrColsToCopy = Array(1, 3, 4, 8, 25, 16, 17, 15) 'which columns to copy ?
    Set c = Sheets("MasterSheet").Range("Y5")  'Start search in Row 5
    LCopyToRow = 10 'Start copying data to row 10 in DepartmentSheet
 
    While Len(c.Value) > 0
 
        'If value in column Y ends with "2540", copy to DepartmentSheet
        If c.Value Like "*2540" Then
 
            LCopyToCol = 1
 
            Sheets("DepartmentSheet").Cells(LCopyToRow, LCopyToCol).EntireRow.Insert shift:=x1Down
 
            For x = LBound(arrColsToCopy) To UBound(arrColsToCopy)
 
                Sheets("DepartmentSheet").Cells(LCopyToRow, LCopyToCol).Value = _
                               c.EntireRow.Cells(arrColsToCopy(x)).Value
 
                 LCopyToCol = LCopyToCol + 1
 
            Next x
 
            LCopyToRow = LCopyToRow + 1 'next row
 
        End If
 
        Set c = c.Offset(1, 0)
 
    Wend
 
    'Position on cell A5
    Range("A5").Select
 
    MsgBox "All matching data has been copied."
 
    Exit Sub
 
Err_Execute:
        MsgBox "An error occurred."
 
End Sub

Basically it takes the information I want from my master data sheet and transfers to individual sheets within the same workbook. It transfers what I want in a way that I am satisfied with - these aspects do not need to change. I would, however, like to add something in to decrease the size of the workbook. Currently, there is a master sheet on top with approximately 30 worksheets behind it for each department that I use this for. I would like to consolidate this process so that the workbook would only have three sheets in it at any given time. The first worksheet would be the master data sheet; the second would be a template; and the third sheet would be generated by the macro each time it is run. The individual sheets now are all formatted exactly the same and pull exactly the same data, so i think a template could work well. What I want is to run the macro, have it open a thirs worksheet with the template's form, and then carry the information to the newly created third sheet in the same manner as it does now. Does this make sense?