CODEWITHSUNDEEP
vbaexcelpaste
zuokuok
zuokuok

Reputation: 431

Simple Excel VBA macro: error 1004 method of Range class failed

In Excel 2013:

I created a module with the following code:

Sub PasteFormatting()
    ActiveCell.PasteSpecial (xlPasteValues)
End Sub

In the macros, I set PasteFormatting as CTRL + V However, when I select a cell in my sheet, and do "ctrl + v", I get the error:

Run-time error '1004': PasteSpecial method of Range class failed

This is the first time I use macros and nowhere have I found something so basic to help me.

All I want to do is copy something in my clipboard, go in Excel, select a cell, and paste without changing the format of my cell.

Upvotes: 1

Views: 623

Answers (2)

L42
L42

Reputation: 19727

Your code works. You will generate such error if nothing is copied in the clip board before executing the code. Same thing happens when you paste something without copying anything first using built in Ctrl+V. If you do that you will hear a beep. To handle your error and emulate such try:

Sub PasteFormatting()
    On Error Resume Next
    ActiveCell.PasteSpecial (xlPasteValues)
    If Err.Number <> 0 Then Beep
    On Error GoTo 0
End Sub

Upvotes: 0

curious
curious

Reputation: 1500

If you are looking for pasting without losing destination formatting even if used Ctrl + V, just put the below code under the Microsoft Excel Objects "ThisWorkbook" (i.e. not under any module).

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoString As String, srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
    If UndoString = "Auto Fill" Then
        Set srce = Selection
        srce.Copy
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
    Else
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

btw, I done't take any credit for this as it's widely available in the internet.

Upvotes: 1

Related Questions