CODEWITHSUNDEEP
excelerror-handlingcopy-pastems-wordvba
JumboFive
JumboFive

Reputation: 45

Error 1004: Microsoft Excel cannot paste the data

I am having trouble copying and pasting some excel charts as pictures and recieving the error: "1004 "Microsoft Excel cannot paste the data." My code finds a premade chart, updates the range, copies the chart, and pastes it into another sheet. The weird thing is that this error is inconsistent where sometimes it will paste a certain chart as a picture and sometimes it won't. I can use similiar code to copy to word with no problems, and if I use the basic copy function, it can repaste as a chart.

This is the functions I am using to copy a chart and paste as a picture:

chtObj.CopyPicture xlScreen, xlPicture
 PasteSheet.Paste

If there is something obvious I am missing or if there any work arounds I am open to ideas

Thank you

Upvotes: 0

Views: 8185

Answers (2)

Rosary Girl
Rosary Girl

Reputation: 1

The only thing that worked for me was Paste Special then "XML Spreadsheet". It preserved the integrity of the spreadsheet with my dates and dollar amounts and links to websites.

Upvotes: 0

JumboFive
JumboFive

Reputation: 45

I have found that for very large points of data it is very easy for excel to lose reference to those points. So if you copy and paste a picture I believe it still in some way keeps a link to those data points which then could be volatile and cause the 1004 error to pop up. My Work around with the help of Floris, chuff, and other SO members was to copy the chartArea and paste using the pastespecial method

ActiveSheet.ChartObjects(chtName).Activate
ActiveChart.ChartArea.Copy
PasteSheet.Select
PasteSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False

Unfortunately it seems the only way to paste to another sheet using pastespecial formatted to bitmap is to select the sheet first paste then re-select the sheet you copied from.

Upvotes: 2

Related Questions