2

It seems a normal question and I have searched and tried many suggestions here but the error persists. I want to copy a "case1" sheet from current workbook to an existing workbook(file name is "workbook2.xlsx", it has a worksheet named "case2"), then save the workbook and close it. Sometimes it works well, but most of the time I kept getting the same error. In fact I did not change any code so I don't know where went wrong.

The error shows "The object invoked has disconnected from its clients". It always breaks in the same place:

ThisWorkbook.Sheets("case1").Copy Before:=ActiveWorkbook.Sheets("Case2")

Sub CopySheet()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False



    'open existing "workbook2.xlsx"
    Workbooks.Open filename:="c:\workbook2.xlsx"

    'copy a sheet named "case1" from current workbook to "workbook2.xlsx" which already has a sheet named "case2"
    ThisWorkbook.Sheets("case1").Copy Before:=ActiveWorkbook.Sheets("Case2")

    'close "workbook2.xlsx"
    Workbooks("workbook2.xlsx").Activate
    ActiveWorkbook.Close SaveChanges:=True

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub
CallumDA
  • 12,025
  • 6
  • 30
  • 52
stone red
  • 21
  • 2
  • 5

1 Answers1

2

Always set references to sheets and workbooks, then there is no chance of confusing which is which. This code does the same thing and should not give you any errors.

Sub CopySheet()
    Dim wb1 As Workbook, ws1 As Worksheet 'Source
    Dim wb2 As Workbook, ws2 As Worksheet   'Target

    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("case1")
    Set wb2 = Workbooks.Open("c:\workbook2.xlsx")
    Set ws2 = wb2.Sheets("case2")

    ws1.Copy Before:=ws2
    wb2.Close True
End Sub
neuralgroove
  • 580
  • 4
  • 12