3

I have several macros which copy and paste images when certain cell values are changed.

Stepping through the code works fine but running by addressing a cell sometimes gives the following error:

Run-Time error '1004':

Paste method of Worksheet class failed.

I use the following code to copy images:

    With Sheets("Quote")
    .Shapes("ExampleQuoteImage").Copy
    .Activate
    .Paste Destination:=Range("A102")
End With

The error doesn't occur on my development machine but does on some users machines.

It doesn't break at the same line every time but will break at any points where an image is copied. The rest of the macro runs normally if run it from the point at which it breaks.

Could it be a concurrency error or something to do with the clipboard?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
evoandy
  • 630
  • 4
  • 16
  • 31
  • 1
    Could it be that the sheet isn't active when the copy is done? I'd move your `.Activate` up one line to check. – Nick Perkins Jan 10 '13 at 12:15
  • The relevant sheet is selected earlier in the module so should already be active when the copy is done. – evoandy Jan 10 '13 at 13:44

2 Answers2

2

Try this

With ThisWorkbook.Sheets("Quote")
    .Shapes("ExampleQuoteImage").Copy
    DoEvents
    .Paste Destination:=.Range("A102") 
End With
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    The debugger stops on the Paste line so putting DoEvents after it won't do anything. I don't think it's a problem with the code as it works fine on my pc and many others. Why would it not work for certain computers? – evoandy Jan 10 '13 at 13:49
  • That is not the only thing.. .I have removed `.Activate` and instead of that added `.Range("A102")` – Siddharth Rout Jan 10 '13 at 13:51
  • Can you please explain why this would stop the run-time error from occuring? – evoandy Jan 10 '13 at 13:55
  • http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 – Siddharth Rout Jan 10 '13 at 14:00
  • I know using `Activate` and `Select` are bad practice but I have tried using code similar to what you have posted and I still get the same error. Therefore I don't think it is the `.Activate` which is causing an issue. – evoandy Jan 10 '13 at 14:12
  • Hmmm... In that case it is very difficult to find what could be the reason since it is happening with just few users. The code works for me as well. Do you think you can find out what the user is exactly doing when they get the error? – Siddharth Rout Jan 10 '13 at 14:18
  • the users are doing the same as me so in theory there is no reason why they should get an error. Could it be do to difference in computer spec? Could the code be trying to paste the image before it has finished copying it? – evoandy Jan 10 '13 at 14:36
  • I doubt it but you can give it a shot... put `DoEvents` after the copy statement. – Siddharth Rout Jan 10 '13 at 14:40
  • I noticed that if I delete an image and try to run a macro which copies it I get the same error. Therefore, maybe the copied image is being cleared form the clipboard between being copied and being pasted, perhaps by an `Application.CutCopyMode=False` somewhere. Does copying an image trigger events in a worksheet? – evoandy Jan 10 '13 at 15:37
  • No Copying will not trigger a event. If the macro is running and the user presses say ctrl + C say in notepad to copy something then that will also clear the clipboard. – Siddharth Rout Jan 10 '13 at 15:50
  • You can check if the clipboard has the image or not before pasting? See this http://www.cpearson.com/excel/Clipboard.aspx – Siddharth Rout Jan 10 '13 at 15:56
  • I've checked the clipboard when it breaks on the paste line and the copied image is in the clipboard. If you run the macro after it breaks, it works properly. What else could cause an issue pasting? I'm out of ideas as to what it may be. – evoandy Jan 10 '13 at 16:20
  • 1
    Did you try adding `doevents` after copy? – Siddharth Rout Jan 10 '13 at 16:25
  • If `Doevents` doesn't work then let me know and I will give you another piece of code to try. – Siddharth Rout Jan 10 '13 at 16:28
  • I've managed to test it on a users machine and `DoEvents` seems to work. Can you explain why this works as I would be interested to know? Is it due to the macro trying to paste before it had copied the image? – evoandy Jan 11 '13 at 09:51
  • Yup seems like the copy operation had not finished completely and hence it couldn't find anything to paste. Amended the above code to move `doevents` on top – Siddharth Rout Jan 11 '13 at 10:15
0

The error occurs because of a clipboard error. The solution is to clear the image clipboard. I do this by adding the following code:

            With [a1]
                .Select
                .Copy [a1]
                Application.CutCopyMode = False
            End With

This code simply copies a1 to a1 and makes sure the copymode is off. It results in the clipboard to be cleared. Do use in combination with DoEvents though

Jeroen
  • 1