Runtime error 1004 when copy paste shapes | Excel forum

Resolved · Medium Priority · Version 365

Thu 17 Mar 2022, 15:26 Delegate Zeeshan said...

Zeeshan has attended:
Excel VBA Intermediate course

Runtime error 1004 when copy paste shapes

Hi, I'm getting some random run-time error messages (i.e. 1004 Method 'Paste' of object'_Worksheet' failed) when I run the code below (usually at '.paste'), which aims to copy shapes from one worksheet (wsTest) to another (wsMain). The errors are random because I get the error sometimes, not everytime I run the code. It also seems to work fine on my old computer but not the new computer (both have Office 365 but slightly different versions). I'm struggling to solve the issue so will really appreciate any advice you are able to provide. Thanks.

Sub TestItem()

Set wsMain = Application.ThisWorkbook.Worksheets("Main")
Set wsTest = Application.ThisWorkbook.Worksheets("TestItem")

wsMain.Range(wsMain.Cells(30, 3), wsMain.Cells(150, 20)).Select
Selection.Delete Shift:=xlToLeft

wsTest.Select

wsTest.Shapes.Range(Array("con5a", "shpLink1")).Select
Selection.Copy

wsMain.Activate
wsMain.Range("C30").Select
wsMain.Paste

wsTest.Select

wsTest.Shapes.Range(Array("ShpNote1")).Select
Selection.Copy

wsMain.Activate
wsMain.Range("C33").Select
wsMain.Paste

wsMain.Range("A1").Select

End Sub

Mon 21 Mar 2022, 19:06 Trainer Jens said...

RE: Runtime error 1004 when copy paste shapes

Hi Zeeshan,

Thank you for the forum question.

I have been looking at your code and the error message.

One thing you should do, is to declare your variables and also create variables for your shapes. This will reference the shapes early in the code and may be the answer to the problem.

The problem is that the error message you get can be many things.

I will probably have to see the file to help you.

If it is possible, can you send a copy to:

info@stl-training.co.uk

Please write in the subject that the file is for me.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 22 Mar 2022, 14:52 Trainer Jens said...

RE: Runtime error 1004 when copy paste shapes

Hi Zeeshan,

I am sorry. I am not able to find a solution. I get a lot of different errors from your code and I cannot tell you what is wrong.

I have Googled, I have tested if it is a memory issue, I have tried to break the code and it copies your shapes and paste, so it is wrong that debug points at the paste line as the problem.

I have been through a lot of the code in many of the other procedures, but I couldn't find the problem.

One thing I spotted: You call a procedure Clear. If you call variables or procedures VBA words you can get a lot of problems.

I cannot understand why the code is working on old PCs and not a new one. It doesn't make sense to me. And it is working sometimes???

As I said I am sorry, I cannot help you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Adding Rows or Columns in an Excel 2010 Worksheet

If you want to add a row to an Excel spreadsheet, these are the simple steps you should take:

With your mouse, right click on the row header below where you want the new row to be added. Then, click Insert.

Follow exactly the same steps if you want to add a column to an Excel worksheet, right click on the column header, choose Insert and the new column will be inserted to the left of the selected column.

View all Excel hints and tips