Error 1004 paste special - Excel VBA

Hi,

When I add "Selection.ClearContents" in the code, I have an error message (1004 paste special) on :
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False.

Without that code "Selection.ClearContents", it's working.

How to clear the cell contents?

Thanks

'Copy
    Sheets("Corriger les donnees").Select
    Columns("D:k").Select
    Selection.Copy
    
    'Paste
    Sheets("Exporter les donnees").Select
    Sheets("Exporter les donnees").Activate
    Sheets("Exporter les donnees").Columns("A:h").Select
    Selection.ClearContents '??????
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
    Application.CutCopyMode = False

Open in new window

Karl001

Asked: 2011-09-09

Advertise Here

Microsoft Excel

2 Solutions

LVL 24

StephenJRCommented: 2011-09-09

I think the CC interrupts the copy/paste. Try this:

With Sheets("Exporter les donnees").Columns("A:h")
    .ClearContents
    Sheets("Corriger les donnees").Columns("D:k").Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End With

Open in new window

LVL 93

Patrick MatthewsCommented: 2011-09-09

ClearContents emptied the copy buffer. Try:

    With Worksheets("Exporter les donnees")
        .Range("A:h").ClearContents
        Worksheets("Corriger les donnees").Range("D:k").Copy
        .Range("A:h").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
  
    Application.CutCopyMode = False

Open in new window

LVL 93

Patrick MatthewsCommented: 2011-09-09

Wow, Stephen and I were thinking pretty much the exact same thing :)

Karl001Author Commented: 2011-09-09

Soory I gave the point at the wrong solution. ( 8:36)

How can I correct that

Karl001Author Commented: 2011-09-09

Not the wrong solution, but the first one
How can correct the situation

LVL 24

StephenJRCommented: 2011-09-09

Indeed Patrick, except emptying the copy buffer is much better!

You can request attention. I think the fairest thing would be to split the points 50-50.

LVL 93

Patrick MatthewsCommented: 2011-09-09

Karl001,

I'd be happy with a split as well.

If you click the Request Attention link, you can ask the Mods to reopen the question.

Patrick

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

Error 1004 paste special - Excel VBA

Join & Write a Comment Already a member? Login.

Featured Post