Method 'Copy' of object '_Worksheet' failed

Question

For the first time I now faced the error

Method 'Copy' of object '_Worksheet' failed

I heard that this is a quite common error but I was not able to find a solution. When I searched trough SO, I found many posts about the

'Range' of object failed-Error.

Is this error caused by memory limitations of the workstation or is it a software related error?

Anyone got an idea to work around that problem?

In case it is of interest that is my VBA code:

Private Sub CommandButton1_Click()
MsgBox "Message"

  Dim wb As Workbook
  Set wb = Workbooks.Add(xlWBATWorksheet)
  wb.Sheets(1).Name = "deletethissheet"

  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    ws.Copy After:=wb.Sheets(wb.Sheets.Count)
  Next

  For Each ws In wb.Worksheets
    ws.UsedRange.Formula = ws.UsedRange.Value

    Dim sh As Shape
    For Each sh In ws.Shapes
      sh.Delete
    Next

  Next

  For Each Link In wb.LinkSources(xlLinkTypeExcelLinks)
  wb.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
  Next

  wb.Sheets("deletethissheet").Delete
  wb.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", "_" & Format(Date, "yyyymmdd") & ".xlsx"), xlOpenXMLWorkbook
  wb.Close SaveChanges:=False

End Sub

ws.Copy After:=wb.Sheets(wb.Sheets.Count) Is the cause of the error.

Answer 1

Its a year old post, but in-case anyone else finds their way here, I received this error too and solved it. I was trying to use the .copy function on a hidden worksheet. As soon as I unhid the sheet, the macro functioned without error. By adding Worksheets("Sheet1").visible = True before .Copy code and Worksheets("Sheet1").visible = False at the end of the code it fixed the error for me.

Answer 2

This is quite old but I also found a possible cause on the official MS documentation:

Source and Destination must be in the same Excel.Application instance, otherwise it will raise a runtime error '1004': No such interface supported, if something like Sheet1.Copy objWb.Sheets(1) was used, or a runtime error '1004': Copy method of Worksheet class failed, if something like ThisWorkbook.Worksheets("Sheet1").Copy objWb.Sheets(1) was used.

Source: https://learn.microsoft.com/en-us/office/vba/api/Excel.Worksheet.Copy

This is definitely the cause in my case. Hope it helps you too!

EDIT: Instead I just copied the Range.CurrentRegion starting from A1 to the other worksheet and that seemed to be working across multiple application instances. Example mockup:

        Dim rg As Range
        Dim xl As Application
        Dim wb As Workbook
        
        Set xl = New Excel.Application
        Set wb = xl.Workbooks.Open("C:\foo.xlsx")
        Set rg = wb.Worksheets(1).Range("A1").CurrentRegion
        
        rg.Copy ThisWorkbook.Worksheets(1).Range("A1")