Excel VBA Error "The picture is too large and will be truncated" when closing file

Question

I have been working on this project for a long time and am suddenly getting a new error whenever I close my Excel file. I get the error twice "The picture is too large and will be truncated." There is no picture in my file. I am pasting formats.

This seems to be one of the Excel "Unsolved Mysteries".

I am using MS Office Professional Plus 2010 on Windows 7.

I have researched this and tried the following:

1. Deleted all %temp% files
2. Ran CCleaner
3. Set CutCopyMode = False after all paste special (formats)
4. Went to add/remove programs and reconfigured Office to stop the Clip Organizer from running. (Control Panel\Programs\Programs and Features -> MS Office Professional Plus 2010 -> Change -> Add or Remove Features -> Office Shared Features -> Clip Organizer -> Not Available, etc.)
5. Rebooted

None of that helped, so I narrowed down the source of the problem by commenting out function and subroutine calls, running the program, saving and then pressing "x" to close. I did this until I found the right sub. Then I commented out all the lines of the sub and added them back in one logical chunk at a time until I found the problem area. Here it is:

' *********** APPLY BASIC ROW FORMATTING FROM TEMPLATE ***********
' Copy basic row formatting from template and paste over all rows
wksTemplate.Rows(giHEADER_ROW + 1).Copy
myWS.Rows(lFirstRow & ":" & lLastRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

The paste contains formatting only - colors, borders, number formats, wrapping etc. It probably pastes on a range of 200 rows on average. I have not changed these 3 lines of code in months. Why now?

Has anyone solved this mystery?

Thanks, Shari

Answer 1

I got this error after copying a range and then using a set of pastespecial calls:

    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValuesAndNumberFormats
    .PasteSpecial xlPasteFormats

solution was to copy an empty cell and pastespecial xlvalues back into itself:

' to avoid the message on closing the book - "picture is too large and will be truncated", copy and paste a singe empty cell
ThisWorkbook.Worksheets(1).Cells(1, 1).Copy
ThisWorkbook.Worksheets(1).Cells(1, 1).PasteSpecial xlValues

' clear clipboard
Application.CutCopyMode = False