"Copy method of Worksheet class Failed" - Excel VBA

[dale_albiston]

problem:
I'm trying to copy a wide range of sheets under macro control, well actually two sheets being copied & configured to make a report template.

The code works fine, upto a point.

Sub BuildTemplate()
    ' scan set list looking for sets to include
    Dim Row As Integer
    Dim MySheet As Worksheet
    
    Dim junk As String
    
    ' remove all sheets *except* the control sheet and the templates
    Application.DisplayAlerts = False
    For Each MySheet In Application.Worksheets
        If MySheet.Name <> "Controls" And MySheet.Name <> "Template-3" And MySheet.Name <> "Template-4" Then
            MySheet.Delete
        End If
    Next
    Application.DisplayAlerts = True
    
    
        For Row = 4 To 47
            If Worksheets("Controls").Cells(Row, 1) = "Yes" Then
                ' set is marked to copy
                
                Worksheets("Template-3").Copy before:=Worksheets("Template-3")
                Set MySheet = Worksheets("Template-3 (2)")
                MySheet.Range("d6") = Worksheets("Controls").Cells(Row, 2)
                MySheet.Range("h6") = Worksheets("Controls").Range("d4")
                MySheet.Range("d8") = Worksheets("Controls").Range("d7")
                MySheet.Name = Worksheets("Controls").Cells(Row, 2) & "-Data"
                
                Worksheets("Template-4").Copy before:=Worksheets("Template-3")
                Set MySheet = Worksheets("Template-4 (2)")
                MySheet.Range("C2") = Worksheets("Controls").Cells(Row, 2)
                MySheet.Range("E2") = Worksheets("Controls").Range("d4")
                MySheet.Range("C3") = Worksheets("Controls").Range("d7")
                MySheet.Name = Worksheets("Controls").Cells(Row, 2) & "-Summary"
            End If
 
        Next Row
    
    MsgBox ("Templates Created for Selected Sets")
    
End Sub

It runs, and runs well for small numbers of sheet to copy,

the 'controls' page holds a two column list, where by a 'yes' or 'no' is specified next to the target sheets name, it copies the two template pages, configures them and renames them, then moves on to the next one.

so far its producing around 26 copies ok, then throwing an error.

Runtime Error 1004
"Copy method of Worksheet class Failed"

the help system being somewhat useless with messages like this, the debug window points to one of the two 'worksheet(x).copy' lines, not always the same one. Its not the sheet names either since it doesn't matter which of the records I select it gets so far then dies.

Given this function has worked fine upto this point, building the first 26 copies fine this is somewhat confusing.

this alos nukes the copy function totally for coping worksheets manually, it just doesn't do anything until excel is closed & restarted. Deleting sheets manually doesn't help so I doubt its an internal sheet limit (is there a limit on how many sheets you can have?)

any ideas on what could be causing this?

naturally an ideas on how to resolve it would also be welcome :-)