Pasting selection into sheet in another workbook fails if excel is opened in another instance

[tomleitch]

I have a spreadsheet that imports data from a different workbook to import and update items in the destination book by pasting data from the second workbook...

It all works absolutely fine - although in some cases when the second workbook (with the data to import) is opened, it opens in a new instance of excel rather than a window of the same version.

This means that when you copy the data to the clipboard it is handled in a different way and the macro crashes.

This microsoft article mentions the issue:

https://docs.microsoft.com/en-us/office/troubleshoot/excel/cannot-paste-attributes

The second spreadsheet is normally opened straight from an email - so is there any way to stop excel opening this second instance? or to modify my macro code to handle this?

An excerpt of the code that I am using is:

If Application.ClipboardFormats(1) = -1 ThenMsgBox "Nothing to paste!"
Exit Sub


End If
 


ThisWorkbook.Sheets.Add
ActiveSheet.Name = "UPDATE TOOL2"
ActiveSheet.Columns("G").ColumnWidth = 18
ThisWorkbook.Sheets.Add
ActiveSheet.Name = "UPDATE TOOL"
ActiveSheet.Columns("G").ColumnWidth = 18


Sheets("OPS PLANNER").Activate


Application.ScreenUpdating = True




'On Error GoTo 90


Sheets("UPDATE TOOL").Activate


    Set ws1 = Sheets("OPS PLANNER")
    Set ws2 = Sheets("UPDATE TOOL2")
    Set Ws3 = Sheets("ARCHIVED")
   


ThisWorkbook.Sheets("UPDATE TOOL").Range("A1").PasteSpecial






Box2 = MsgBox("Do you wish to import displayed data?", vbOKCancel, "DATA IMPORT")






If Box2 = vbOK Then

I have tried using normal paste rather than paste special but it still fails.


Any suggestions?


Thanks
Tom

 

[tomleitch]

Managed to fix the issue by using the PasteValues when pasting

Line changed to

ThisWorkbook.Sheets("UPDATE TOOL").Range("A1").PasteSpecial Paste:=xlPasteValues

And now it works and has stopped it from crashing.