excel 2013 copy/paste object - Error 1004 Paste method of Worksheet class failed

Question

Code Objective

I have an image object of my company logo on a "Control Sheet" named as picCompanyLogo. I have several worksheets and each has the same code in the Worksheet_Activate event to copy the company logo from the "Control Sheet" and paste it into the heading in A1. I am calculating the size of my row and the size of the company logo image to ensure that it is always placed on the right hand side of sheet.

Code Issue

The code has been working fine on all versions of Excel for both 32Bit and 64Bit versions. I have a user who is running Excel 2013 and the code terminates with the error 1004 - Paste method of Worksheet class failed when pasting the object into "Range.A1".

When I step through the code (F8), there is no issue but if I set a Stop just before the code is executed and then F5, it generates the same error.

If Sheets("ControlSheet").Shapes("picCompanyLogo").Name <> "picCompanyLogo" Then
   MsgBox "[MODULE: Worksheet Change(Header):Worksheet_Activate]" & vbCrLf & _
   "The CompanyLogo (picCompanyLogo) is missing from the Control worksheet (ControlSheet)." & vbCrLf & vbCrLf & _
   "The application will continue but please contact IT Support to correct the issue.", _
   vbOKOnly, _
   "MISSING COMPANY LOGO"
Else
   Application.EnableEvents = False

   ThisWorkbook.Sheets("ControlSheet").Shapes("picCompanyLogo").Copy
   ThisWorkbook.ActiveSheet.Paste Range("A1")

   Application.CutCopyMode = False

   lngCompanyLogoWidth = Sheets("ControlSheet").Shapes("picCompanyLogo").Width
   With ThisWorkbook.ActiveSheet.Shapes("picCompanyLogo")
       .Left = lngShapeWidth - lngCompanyLogoWidth - 2
       .Top = ActiveSheet.Range("B:" & strLastColLetter).Top + 2
   End With

   Application.EnableEvents = True

End If

I have inserted the following lines:

Application.EnableEvent
Application.CutCopyMode

Both have had no effect on the error in Office 2013 and the process continues to work as expected in other versions of Excel.

When stepping through the code, if I'm too fast on the F8, the Paste appears to work i.e. no error is reported, but the process subsequently fails on the lines:

   With ThisWorkbook.ActiveSheet.Shapes("picCompanyLogo")
       .Left = lngShapeWidth - lngMulberyLogoWidth - 2
       .Top = ActiveSheet.Range("B:" & strLastColLetter).Top + 2
   End With

The error indicates that the shape could not be be located on the worksheet, which leads me to think the Paste didn't do what I expected.

When you F8 thru the paste code, can you open the ActiveSheet and see if the logo pasted? Also, in your last code snippet, which line specifically does the error occur on? — astidham2003
I have two monitors one with the sheet and the other with the code and I can see that as I F8 through, the image is pasted into the top left corner of A1. To answer your second point, it errors on the With ThisWorkbook.ActiveSheet.Shapes("picCompanyLogo") — DuaneRRR

DuaneRRR DuaneRRR · Accepted Answer · 2015-08-12T11:00:21

I have tried this on another users PC, also running Office 2013 and I'm not encountering the same issue. I can only put this down to some quirk on the original users implementation of Office 2013.

excel 2013 copy/paste object - Error 1004 Paste method of Worksheet class failed

1 Answers