Copying Multiple Sheets Via Macro Fails After X Copies

Hi,

I have some code in an excel macro which is copying numerous worksheets and I am trying to overcome the classic "Run-Time Error 1004: Copy method of worksheet class failed"

If I could save my Workbook, Close, Reopen and continue from where I leftoff in the code then I believe that would solve the problem but I am not sure if this is possible.

Any help would be greatly appreciated as I have been searching forums for a couple of days now and feel like I am going round in circles.

Re: Save Close Reopen Workbook & Continue Running Code

Thanks for your replies
Appologies, I did read the rules regarding titles and thought that my title would be better than "Copy Method of Worksheet Class failed"

The problem I am having is described here
http://support.microsoft.com/kb/210684

I have posted the microsoft resolution below because it is easier to discuss than posting my own code:

Sub CopySheetTest()
    Dim iTemp As Integer
    Dim oBook As Workbook
    Dim iCounter As Integer
    
    ' Create a new blank workbook:
    iTemp = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oBook = Application.Workbooks.Add
    Application.SheetsInNewWorkbook = iTemp
    
    ' Add a defined name to the workbook
    ' that RefersTo a range:
    oBook.Names.Add Name:="tempRange", _
        RefersTo:="=Sheet1!$A$1"
            
    ' Save the workbook:
    oBook.SaveAs "c:\test2.xls"
    
    ' Copy the sheet in a loop. Eventually,
    ' you get error 1004: Copy Method of
    ' Worksheet class failed.
    For iCounter = 1 To 275
        oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
        'Uncomment this code for the workaround:
        'Save, close, and reopen after every 100 iterations:
        If iCounter Mod 100 = 0 Then
            oBook.Close SaveChanges:=True
            Set oBook = Nothing
            Set oBook = Application.Workbooks.Open("c:\test2.xls")
        End If
    Next
End Sub

This resolution does not work for me because my workbook path is stored as a variable (rather than just enetering the path name ("c:\test2.xls") as above). When i close my workbook I am unable to reopen because the path name which is stored as a variable is lost.

Re: Save Close Reopen Workbook & Continue Running Code

Shg, Thanks for your reply.

Isn't the kb article you refer to the one i have linked to in my last post?

I am unable to close and reopen my workbook as you suggest because my code always stops working when the original file closes, please see example below

Function Macro1()
Set SpecFile = ThisWorkbook
SpecPath = SpecFile.FullName
SpecFile.SaveCopyAs ("temp.xls")
Set SpecTemp = Application.Workbooks.Open("temp.xls")
Application.Run "temp.xls!SaveTemp", SpecPath, SpecFile
'After running function above i want it to come back to here and continue running macro
End Function


Public NewVar1, NewVar2


Sub SaveTemp(SpecPath, SpecFile)
NewVar1 = SpecPath
Set NewVar2 = SpecFile
SpecFile.Close SaveChanges:=False
'Because original workbook closes the code stops running here, i would like it to continue
Set SpecFile = Application.Workbooks.Open(NewVar1)
ThisWorkbook.SaveAs Filename:=NewVar1
End Sub

Re: Save Close Reopen Workbook & Continue Running Code

Quote from shg

......you need the code that does it to run in a different workbook.

In the last example I gave I have saved a copy of the original file as "temp.xls", opened "temp.xls" and then run the sub called SaveTemp (within "temp.xls"). Since "temp.xls" is a different workbook i thought that would be sufficient?

Do you think there might be some changes I can make so that SaveTemp keeps running after the original workbook (SpecFile) closes?

Thanks again