Excel VBA: Intermittent "PasteSpecial method of Range class failed" error #

Proposed solutions abound for "Run-time error '1004': PasteSpecial method of Range class failed", which, intermittently yet relentlessly, plagued an XLSM spreadsheet when pasting user-selected rows via VBA.

Buried in a previously 0-rated Stack Overflow question and answer pair, PASUMPON V N graciously unveiled the mystery:

Since you are copying large amount of data, you need to clear the memory after...pasting the values.

Please use the below code at the end of pastespecial line

Application.cutcopymode = False
Application.cutcopymode = True

It appears that the second line can, and probably should, be excluded:

Application.CutCopyMode = True either does nothing at all or it does the opposite of what you think it does.

/windows | Oct 28, 2021

Subscribe or visit the archives.