• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

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

0
Karl001
Asked:
Karl001
  • 3
  • 2
  • 2
2 Solutions
 
StephenJRCommented:
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

0
 
Patrick MatthewsCommented:
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

0
 
Patrick MatthewsCommented:
Wow, Stephen and I were thinking pretty much the exact same thing :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

How can I correct that
0
 
Karl001Author Commented:
Not the wrong solution, but the first one
How can correct the situation
0
 
StephenJRCommented:
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.
0
 
Patrick MatthewsCommented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now