vba - excel VBA错误: The object invoked has disconnected from its clients

Question

这似乎是一个正常的问题，我在这里搜索并尝试了许多建议，但错误仍然存​​在。我想将“case1”工作表从当前工作簿复制到现有工作簿(文件名是“workbook2.xlsx”，它有一个名为“case2”的工作表)，然后保存工作簿并关闭它。有时它运作良好，但大多数时候我一直收到同样的错误。实际上我没有更改任何代码，所以我不知道哪里出错了。

错误显示“调用的对象已与其客户端断开连接”。它总是在同一个地方中断:

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

Answer 1

始终设置对工作表和工作簿的引用，这样就不会混淆哪个是哪个。这段代码做同样的事情，不应该给你任何错误。

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