VBA Code Image Copy/Paste Error 1004

Question

I've been having trouble copy and pasting images from one worksheet to another. This code works for PCs but not for Macs! I'm getting an error 1004 when I run it on Macs.

This is an issue with copy/pasting images! I've looked over many forums and nothing is working!

Below is my code:

    Dim sh As Shape
    For Each sh In Sheets("Settings").Shapes
        Debug.Print sh.Name
        If sh.Name = reportname Then  ' give the name of your Picture here
            sh.Copy
                With Sheets(reportname)
                    .Select
                    .Range("A1").Select
                    .PasteSpecial
                End With
            Application.CutCopyMode = False
        End If
    Next

The error occurs on the .PasteSpecial line and once again is error 1004.

Have you tried specifying the Format argument? e.g. PasteSpecial Format:= "Picture (PNG)". — L42
Hi there, thanks for the reply! I just tried that and it unfortunately doesn't work. The weird thing is that it actually copy and pastes the first image properly, but it gives the error 1004 when it attempts to paste the second image. — Mitchell Sanregret
That shouldn't be the case. The image should be pasted on top of the first pic. — L42
I know! But keep in mind this bit of code is within a loop which loops through worksheets. So the idea is to have the images posted on different worksheets depending on whether the image name matches the worksheet name. — Mitchell Sanregret

L42 L42 · Accepted Answer · 2014-09-10T22:48:52

Try this which seems to work at my end.

Sub Test()
    Dim shp As Shape
    For Each shp In Sheets("Settings").Shapes
        If shp.Type = 13 Then
retry:
            shp.Copy
            On Error Resume Next
            Sheets(shp.Name).PasteSpecial
            If Err.Number = 9 Then
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = shp.Name
                GoTo retry
            End If
            On Error GoTo 0
        End If
    Next
End Sub

VBA Code Image Copy/Paste Error 1004

1 Answers