Copy/paste error when another workbook is open: PasteSpecial method of Range class failed

1
votes

I need to transfer one graph several times between sheets.

The graph takes data from a table and the data in the table changes based on a selection from a list.

The macro loops through this list and within each loop copy/pastes the graph as a picture to a new sheet.

The code for copy/paste

Worksheets(redemp).ChartObjects("Chart 6").Copy
pasteRow = pasteRow + 24
Worksheets(pdf).Cells(pasteRow, pasteCol + 1).PasteSpecial xlPasteValues

  1. Is there a better way of transferring the graph between sheets than copy/paste?

  2. When I run the macro (via a VB script which is run via Task Scheduler) and another Excel workbook is already opened VBA throws PasteSpecial method of Range class failed. It only breaks down for chart objects.

1
excelvba

1 Answers

0
votes

The .Copy Destination method is usually the best with large bulky data since it skips the clipboard. I find charts tend to be similar in that they take a lot of processing power to move and edit so this method should work better if you're not using it already. 

ActiveSheet.ChartObjects("Your Chart").Activate
ActiveChart.Copy Destination:=Worksheets("Sheet").Range("Cells")

Another approach would be to create several chart objects and use your code to edit them to each sheet's needs.

For the second question, I've never ran macros through Windows task scheduler so I'm not sure but turning off screen updating or temporarily locking the sheet should fix the problem.

Hope this helps!