Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error 1004 paste special - Excel VBA

Posted on 2011-09-09
8
Medium Priority
?
799 Views
Last Modified: 2012-05-12
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
Comment
Question by:Karl001
  • 3
  • 2
  • 2
8 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 1000 total points
ID: 36510037
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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 36510042
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36510045
Wow, Stephen and I were thinking pretty much the exact same thing :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Karl001
ID: 36510091
Soory  I gave the point at the wrong solution. ( 8:36)

How can I correct that
0
 

Author Comment

by:Karl001
ID: 36510210
Not the wrong solution, but the first one
How can correct the situation
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36510213
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36510921
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question