Macro to paste between workbooks only works in single instance of Excel

Hi again! I have a macro to paste whatever's in a user's clipboard into a specific sheet in a workbook when the user clicks a button (it also does a few other steps). It works fine when both the data workbook (where the user is copying from) and the report workbook (containing the macro, where the user is pasting to) are open in the same instance of Excel.

However, I get an error message "Run-time error '1004': PasteSpecial method of Range class failed" when the data workbook and the report workbook are opened in separate instances of Excel. The Debug brings me to the "Selection.PasteSpecial" row of code.

Here's the code:

Please Login or Register  to view this content.

I was getting a different error at one point, hence the "On Error" code - which was fine to ignore - everything still worked perfectly. However, when I try to ignore this error with similar code, no content gets pasted.

Thoughts? Thanks in advance!

Hi xdrenched,

I believe you need to be in the same session for the more advanced options of "PasteSpecial" to be available. If you are pasting from the clipboard, then I think it can only be pasted as Unicode or Text.

I would suggest if you know the range you are copying, open the reference workbook read-only within your code and copy the pertinent data and then it should paste per your code above and then close the reference workbook once the paste is complete.

Here is some example code of what I'm describing:

Please Login or Register  to view this content.

Hope that helps,

Dan