Run-time error -2147319767 (80028029) ... SOMETIMES

[ColoKevMan]

Hello,

Can anyone help me with why the below code SOMETIMES returns the Automation run-time error and sometimes it works just fine? The Debug highlights the Dest.Sheets("Data-Remarks").Activate line but I know that doesn't necessarily mean that's where things are hanging up.

Does it have to do with the Ranges coding not being explicit or just overall inefficiency of code? I have discovered that if, after receiving the error, Excel is closed then re-opened, the code works fine.

Sub UpdateRemarksjb()
Application.ScreenUpdating = False
Set Orig = Workbooks.Open("filename1 on sharepoint site")
Set Dest = Workbooks.Open("filename2 on sharepoint site")
   Orig.Sheets("Jonathan B").Activate
    Range("A13:Q513").Select
    Selection.Copy
   Dest.Sheets("Data-Remarks").Activate
    Range("A1004:Q1504").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Call ClearClipboard
   Dest.Close savechanges:=True
    Orig.Sheets("Jonathan B").Activate
    Range("A8").Select
Application.ScreenUpdating = True
End Sub

 

[footoo]

I think .Activate sometimes doesn't work when ScreenUpdating has been set to False.
Try this :

Sub UpdateRemarksjb()
Application.ScreenUpdating = False
Set Orig = Workbooks.Open("filename1 on sharepoint site")
Set Dest = Workbooks.Open("filename2 on sharepoint site")
Dest.Sheets("Data-Remarks").Range("A1004:Q1504").Value = _
    Orig.Sheets("Jonathan B").Range("A13:Q513").Value
Dest.Close savechanges:=True
Application.ScreenUpdating = True
("Jonathan B").Activate
Range("A8").Select
End Sub