Macro fails at pasting (Run-time error '1004' Paste method fail)

**SergioS** · 04-07-2010, 06:50 AM

First off, let me start off by saying that I'm not the creator of the macro, so I don't know all the details. I have been given the file and asked to help find a solution, where best to go to ask for help than here right!
The error is "Run-time error '1004' Paste method of Worksheet class failed"
The macro used to work in excel 2003 if I remember correctly, but definetly doesn't in the 2007 version because of this error. I've done some backtracking down the forums and research but still feel rather hopeless, so I'll post the macro in here, should give you sufficient information.

Please Login or Register  to view this content.

Help much appreciated!

**Marcol** · 04-07-2010, 06:55 AM

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers

**Richard Buttrey** · 04-07-2010, 06:57 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Since this is your first post I have done it for you but please remember in the future.

Regards

**Richard Buttrey** · 04-07-2010, 07:07 AM

Hi,

You need to move the

Please Login or Register  to view this content.

code after your ActiveSheet.Paste line.

However better still avoid all the .Selects and address the objects directly. Use code like the following - note this is not complete I show you merely as an example.

Please Login or Register  to view this content.

HTH

**SergioS** · 04-07-2010, 09:06 AM

Allright, I'll give that a try, and the code mistake won't happen again, sorry!

**SergioS** · 04-07-2010, 09:17 AM

Well I moved the

Please Login or Register  to view this content.

after the ActiveSheet.Paste line.

And that seemed to resolve the paste issue.
But after that, further down the macro, I get the same error, but for pastespecial. Thoughts?

Sub Danke6()
'
' Danke6 Macro
' Macro recorded 27-05-2005 by Ernest Smeets
'

'
ActiveSheet.Unprotect
Selection.Font.ColorIndex = 3
Selection.Copy
Range("AG20:AM20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Clear
Range("Z20:AV22").Select
Selection.PrintOut Copies:=2
Range("Z20:AT20").Select
Selection.Copy
Sheets("Liste").Select
Application.CutCopyMode = False
ActiveSheet.Unprotect
Range("A4").Select
ActiveSheet.PasteSpecial Format:="VALU", Link:=False, DisplayAsIcon:= _
False
Range("V4").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("V5").Select
Selection.Copy
Range("V4").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("W5").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("W4:W5"), Type:=xlFillDefault
Range("W4:W5").Select
Range("A4").Select
Selection.EntireRow.Insert
Range("A4").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("TICKET").Select
Range("Z20").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save
End Sub