CODEWITHSUNDEEP
excelvba
Vincenzo
Vincenzo

Reputation: 321

Method 'Copy' of object '_Worksheet' failed

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.

Upvotes: 7

Views: 5522

Answers (3)

Gabriel Gyarmati
Gabriel Gyarmati

Reputation: 11

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

Upvotes: 1

RunnerG
RunnerG

Reputation: 41

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.

Upvotes: 4

Vincenzo
Vincenzo

Reputation: 321

Despite intensive research, I could not find any details about this error. The solution, which turned out to be very easy for me, was to manually copy everything onto a new sheet and start the macro from there. This error may be caused by limited resources or bad cells.

The VBA itself works. If anyone runs into this kind of problem rebuilding the sheet may work.

Upvotes: 1

Related Questions