Excel 2013 cannot copy sheets

Question

When I try to copy sheet manually to new workbook, Excel do nothing
When I try to copy sheet manually to end of workbook, Excel makes empty new sheet.

When I try to copy sheet with VBA:

Sub TransEx()
    Dim TemplatesFolder As String, FileName As String
    TemplatesFolder = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "")
    FileName = TemplatesFolder & "Transactions" & ".xlsx"
    If Len(Dir(FileName)) <> 0 Then Kill FileName

    ThisWorkbook.Worksheets("Baza").Copy

    Application.ActiveWorkbook.SaveAs FileName, FileFormat:=51
End Sub

I got error on ThisWorkbook.Worksheets("Baza").Copy

Run-time error '1004': copy method of worksheet class failed

How to Fix that?

If your first two sentences are true, then there is either a problem with the Excel file or with the Excel application. First I would try open the file and save it with new name and location. Then try copying sheets again in this new file. If it still does not work, then I would try a repair installation of the Excel application. — Axel Richter

Miguel_Ryu Miguel_Ryu · Accepted Answer · 2015-04-22T09:54:50

Try defining the destination for the Copy method.

Sub TransEx()
Dim TemplatesFolder As String, FileName As String
    TemplatesFolder = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "")
    FileName = TemplatesFolder & "Transactions" & ".xlsx"
    If Len(Dir(FileName)) <> 0 Then Kill FileName

    Set wbO = Workbooks.Add

    ThisWorkbook.Worksheets("Baza").Copy Before:=wbO.Sheets(1)


    Application.ActiveWorkbook.SaveAs FileName, FileFormat:=51

End Sub

Excel 2013 cannot copy sheets

1 Answers