Solved

Error 1004 paste special - Excel VBA

Posted on 2011-09-09
8
722 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 250 total points
Comment Utility
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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
Comment Utility
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 92

Expert Comment

by:Patrick Matthews
Comment Utility
Wow, Stephen and I were thinking pretty much the exact same thing :)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

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

How can I correct that
0
 

Author Comment

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

Expert Comment

by:StephenJR
Comment Utility
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 92

Expert Comment

by:Patrick Matthews
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now