Copy worksheets to VBA Copy Error

Question

Hi, I have a problem copying sheets from one book to another in VB. The code that I am working fine with new workbooks, but it breaks down after a while and gives me this error: The "Copy" method of the "Desktop" object failed. Many people suggested saving the book and reopening it when copying. I tried this and still didn’t work. I also checked if it is possible that the name becomes very long. I set the name of the worksheet to the counter before copying it and I still have an error. I am really confused and hope that someone can understand this solution. Also, both books have only 3 worksheets.

'Copies all the worksheets from one workbook to another workbook 'source_name is the Workbook FullName 'dest_name is the Workbook FullName Function copyWorkbookToWorkbook(source_name As String, dest_name As String) As Boolean Dim dest_wb As Workbook Dim source_wb As Workbook Dim dest_app As New Excel.Application Dim source_app As New Excel.Application Dim source_ws As Worksheets Dim counter As Integer Dim num_ws As Integer Dim new_source As Boolean Dim new_dest As Boolean Dim ws As Worksheet Dim regex As String Application.ScreenUpdating = False If source_name = "" Or dest_name = "" Then MsgBox "Source and Target must both be selected!", vbCritical copyWorkbookToWorkbook = False ElseIf GetAttr(dest_name) = vbReadOnly Then MsgBox "The target file is readonly and cannot be modified", vbCritical copyWorkbookToWorkbook = False Else regex = "[^\\]*\.[^\\]*$" 'Gets only the filename copyWorkbookToWorkbook = True If (isWorkbookOpen(source_name)) Then Set source_wb = Workbooks(regExp(source_name, regex, False, True)(0).Value) Else Set source_wb = source_app.Workbooks.Open(source_name) new_source = True End If If (isWorkbookOpen(dest_name)) Then Set dest_wb = Workbooks(regExp(dest_name, regex, False, True)(0).Value) Else Set dest_wb = dest_app.Workbooks.Open(dest_name) new_dest = True End If 'Clean the workbooks before copying the data 'Call cleanWorkbook(source_wb) 'Call cleanWorkbook(dest_wb) 'Copy each worksheet from source to target counter = 0 source_wb.Activate For Each ws In source_wb.Worksheets MsgBox dest_wb.Worksheets.Count ws.Copy After:=dest_wb.Worksheets(dest_wb.Worksheets.Count) counter = counter + 1 Next ws 'Save and close any newly opened files If (new_dest) Then dest_wb.Application.DisplayAlerts = False dest_wb.SaveAs Filename:=dest_name, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges dest_wb.Application.CutCopyMode = False dest_wb.Close End If If (new_source) Then source_wb.Application.DisplayAlerts = False source_wb.SaveAs Filename:=source_name, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges source_wb.Close End If MsgBox counter & " worksheets have been cleaned and copied.", vbInformation + vbOKOnly End If 'Cleanup Set dest_wb = Nothing Set source_wb = Nothing Set dest_app = Nothing Set source_app = Nothing Set source_ws = Nothing Set ws = Nothing End Function Function regExp(str As String, pattern As String, ignore_case As Boolean, glo As Boolean) As MatchCollection Dim regex As New VBScript_RegExp_55.regExp Dim matches As MatchCollection regex.pattern = pattern regex.IgnoreCase = ignore_case regex.Global = glo Set regExp = regex.Execute(str) End Function 

Edit: I meant that “this book is interrupted after a while” is that I can run this code several times (maybe about 30 times). In the end, this error appears "Copy method" of the object "Worksheet" failed, "even if I delete the sheets in the dest_wb file. It points to the line "Copy."

Answer 1

I had similar tasks with copies from a template file. I think this is a memory problem that fires after a certain number of copies and pastes (depending on your system).

Depending on what your worksheets contain, there are several workarounds. I did not have to go through many books, but I found that the following function effectively does the same without any problems.

You can only note a few things that you probably are not helping by creating two new Excel instances each time you copy sheets from one workbook to another. Why you cannot use an instance of Excel, just use at least one instance of Excel.

 Sub CopyWorksheet(wsSource As Worksheet, sName As String, wsLocation As Worksheet, sLocation As String) 'Instead of straight copying we just add a temp worksheet and copy the cells. Dim wsTemp As Worksheet 'The sLocation could be a boolean for before/after. whatever. If sLocation = "After" Then Set wsTemp = wsLocation.Parent.Worksheets.Add(, wsLocation) ElseIf sLocation = "Before" Then Set wsTemp = wsLocation.Parent.Worksheets.Add(wsLocation) End If 'After the new worksheet is created With wsTemp .Name = sName 'Name it .Activate 'Bring it to foreground for pasting wsSource.Cells.Copy 'Copy all the cells in the original .Paste 'Paste all the cells .Cells(1, 1).Select 'Select the first cell so the whole sheet isn't selected End With Application.CutCopyMode = False End Sub 

Answer 2

Yes, I have exactly the same problem in some code that I use, although it never pressed enough for me to do what (apparently) I needed to fix.

The problem is described in this Knowledge Base article. The article assumes that:

To resolve this issue, periodically save and close the book while the copying process is in progress.

I note that you said that you “save and open the book when copying”, but I assume that you do this before you run the code, since I do not see any indication of its execution during the loop. One way to do this inside the loop itself:

Implement error handling with

 On Error Goto 

at an early stage of the procedure; then

Put

 Exit Function ErrorHandler: 

block below. Inside the error handler itself, you will need to check if Err.Number is 1004. If so, close both the source and target books, then reopen them and resume in the line where the error occurred. It would be nice to keep track of how many calls to the error handler have been made, and just discard a certain number to make sure that you don't end an infinite loop.

This is basically an idea that I had to solve my problem, but I never had the time / need to implement it. I would test it before publishing it, but the files are in the office and I do not have access to them.

I would be interested to see how you go if you decide to go this route.

Another option is the one proposed in the KB article, which consists in closing and reopening a book after n iterations. The problem is that it offers 100 iterations, while mine fails after 32 or 33. (It seems that it depends on the size of the sheet, among other things.) There are also times when mine fails after 10 (with exactly same sheets) and the only way to fix this is to close and reopen Excel. (Obviously, this is not a very large option for VBA-based code.)