1

I have code that copies images from 1 workbook to another. Excel decides to stop the code once in a while with:

Error 1004 "PasteSpecial method of Range class failed"

Sometimes it successfully copies all images, but most of the time a few are left out (when on error resume next is set). If I choose "Debug" on the error and then press F8, the image is copied. (but on error resume next does not have the same effect)

Other ways of copying images, like .CopyPicture End result is the same.

    'Code below is in a loop

    vArr3 = Split(Cells(1, i).Address(True, False), "$")
    t = vArr3(0)

    Application.Goto Workbooks(BOM).Worksheets("Blad1").Range("a2")
    Workbooks(BOM).Worksheets("Blad1").Shapes.Range(Array("Afbeelding " & im)).Select

    Selection.Copy

    Workbooks(Template).Worksheets("Sheet1").Activate

    Application.Goto Workbooks(Template).Worksheets("Sheet1").Range(t & rs)
    Workbooks(Template).Worksheets("Sheet1").Range(t & rs).Select

    Workbooks(Template).Worksheets("Sheet1").Range(t & rs).PasteSpecial 'Error here

    rs = rs + 5
    im = im + 2

I expect Excel to always work 100% correctly. The images (and number of) that are left out is random.

Cyril
  • 6,448
  • 1
  • 18
  • 31
R. Voogt
  • 11
  • 3
  • 4
    Probably not your problem but in general, you want to [avoid activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad Jun 26 '19 at 15:39
  • Yes, adding that or removing that does not solve the problem. I'll remove it. – R. Voogt Jun 26 '19 at 15:53
  • Try adding `DoEvents` after the Copy line – Tim Williams Jun 26 '19 at 16:48
  • @TimWilliams That does seem to make the code work (a lot) better. It copies all the images (but in the back of my mind a voice says that it is just luck that it works now :) ), but sometimes a pasted image has a little cross and the text "image cannot be displayed" in it. – R. Voogt Jun 27 '19 at 05:20
  • Yes I've found that `DoEvents` helps but it doesn't fix 100% of issues. I've not seen the "image cannot be displayed" problem though. – Tim Williams Jun 27 '19 at 05:40
  • As expected, after 5 good runs, it fails again. Also, rarely I also get this warning: The key you specified was not found in the collection. – R. Voogt Jun 27 '19 at 06:18

0 Answers0