Bizarre behavior on Excel 2011 (for Mac)

[mcomp72]

I have discovered a bizarre problem with Excel 2011 (for Mac) and cannot for the life of me figure out how to solve it.

I've developed a workbook that needs to work on both Windows & Mac. I've tested some code on Windows, and it works fine. I've tested it on Excel 2019 for Mac and it works fine. However, on Excel 2011 it causes an error. What's bizarre is that if I put in a breakpoint, and then after the code hits the breakpoint, I press F5 (Continue), it works with no error.

Here's the code:

'This will copy all images, except "LeftArrow" and "RightArrow"
For Each S In sourceWS.Shapes

    If S.Name <> "LeftArrow" And S.Name <> "RightArrow" Then
   
        If S.Visible = True And S.Type = msoPicture Then
   
            OriginalName = S.Name
            S.Name = "FixedName"
            S.Copy
            targetWS.Paste
            targetWS.Shapes("FixedName").Top = S.Top
            targetWS.Shapes("FixedName").Left = S.Left
            S.Name = OriginalName
            targetWS.Shapes("FixedName").Name = OriginalName
       
        End If
       
    End If
   
Next S

The line that is causing the error on Excel 2011 is

targetWS.Paste

The error is Run-time 1004: Method 'Paste' of object '_Worksheet' failed.

What's even more bizarre is I have similar code in another sub, and that sub doesn't cause any error on Excel 2011.

Does anyone have any ideas what might be wrong?

 

[MARK858]

Is targetWS the ActiveSheet when the code is run?

 

[mcomp72]

Yep. When the error happened, I took a look at that, and targetWS was the ActiveSheet. Good idea, though. I thought that might have been it, as well.

I even tried setting the ActiveSheet to the sourceWS before the Copy line, and then switched the ActiveSheet to the targetWS before the Paste line. No luck there.

 

[MARK858]

Any sheet protection? have you tried specifying a cell to paste to in the code? Any merged cells?

 

[mcomp72]

Any sheet protection? have you tried specifying a cell to paste to in the code? Any merged cells?

I have confirmed that neither sheet is protected. I have also confirmed that both sheets are "xlSheetVisible".

I tried recording a macro of me copying the image myself, and then I used that code. I replaced this code:

S.Copy
targetWS.Paste

with this:

sourceWS.Activate
ActiveSheet.Shapes.Range(Array("FixedName")).Select
Selection.Copy
targetWS.Activate 'I tried it both with and without this line
targetWS.Select
targetWS.Range("E4").Select
ActiveSheet.Paste

Still getting the same error, this time on the ActiveSheet.Paste line.

There ARE merged cells on both the sourceWS and targetWS. I wasn't aware that that could make a difference. I don't think I can do anything about the merged cells, unfortunately. The sourceWS has been designed by someone else, and the code I've written is designed to copy everything from it to a new sheet in the same workbook. Copying the cells doesn't cause any error; only the pictures.

 

[MARK858]

Please ignore this post