"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 :-)

[dale_albiston]

Further to the above.. I figured a dirty hack, simple have several reports, each with fewer than 26 result sets in it..

went with ten, ran one ok. saved it..
ran the second (another 10), gave it a new name, saved it
the third bombed. as before, even though the origonal sheet is no longer open.

it looks like worksheet(x).copy runs 'z' number of times and thats it without restarting excel... wierd.

[DKenny]

Dale
I just ran your code 5 time on the same workbook with no errors. It produced 88 templates (44 * 2) each time without throwing any errors - so there nothing wrong with your code.

[malik641]

Dale
I just ran your code 5 time on the same workbook with no errors. It produced 88 templates (44 * 2) each time without throwing any errors - so there nothing wrong with your code.

I did the same. Code's working fine for me too.

Think you can attach the workbook that's giving you the problem???

[dale_albiston]

Ahem..

Rename the attached to 'anything'.xls and it should be ok.

the trick is to move to the 'control' sheet, select which set numbers you wish to generate a template for. then click the button, this will delete everything except the control sheet and the two templates, then build the templates as required.

The set numbers relate to trains (yawn), for the purposes of testing the sheet the dates and period ranges are not relevent.

This builds a report template which is then fed to a vb.net program to fill in.

Its good to hear the code works, its probably something related to the machine here and something to do with a library being corrupt or some such rubbish.

Typically it bombs around the 26th set on this machine, if it still all works fine its def this flipping machine.. Grrrr..

Thanks for testing it though :-)