下面的代码有时会产生错误"无法使用粘贴方法"。有时它会,有时它不会——即使我在完全相同的文件上运行宏。
你能找出原因或提出一个更稳定的方法吗?
Set x = srcWbk.Sheets("C").OLEObjects("MCFB")
Set y = x.Duplicate
xName = x.Name
y.Cut
trgtWbk.Sheets("C").Activate
trgtWbk.Sheets("C").Range("O1").Select
With trgtWbk.Sheets("C")
.Paste
.OLEObjects(.OLEObjects.Count).Name = xName
.Activate
End With
这是一个临时答案。当OP看到代码时,它将被删除
复制了大约一百次按钮,没有任何错误。请确保您的trgtWbk
已正确关闭,并且引用是免费的。我看不到整个代码以及如何操作工作簿对象,所以很难判断为什么会出现错误。
尝试重新安排您的代码以最适合下面的模式
Sub Main()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:Users...DesktopBook2.xlsm")
CopyActiveX "cmd1", wb1, wb2
CopyActiveX "cmd2", wb1, wb2
wb2.Save
wb2.Saved = True
wb2.Close
Set wb2 = Nothing
End Sub
Sub CopyActiveX(cName As String, ByRef srcWbk As Workbook, ByRef trgtWbk As Workbook)
Dim x As OLEObject
Dim y As OLEObject
Dim xName As String
Set x = srcWbk.Sheets("C").OLEObjects(cName)
Set y = x.Duplicate
xName = x.Name
y.Cut
trgtWbk.Sheets("C").Activate
trgtWbk.Sheets("C").Range("O1").Select
With trgtWbk.Sheets("C")
.Paste
.OLEObjects(.OLEObjects.Count).Name = xName
.Activate
End With
End Sub