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

Question

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.

curious · Accepted Answer · 2017-06-23 01:42:28Z

1

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.

answered Jun 23, 2017 at 1:42

curious

1,4925 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zuokuok Over a year ago

Doesn't work all the time for some reason. Some clips I paste make a new line in the cell and the cell is empty...

curious Over a year ago

It may happen if external content consists features like wrap text, etc. Try pressing F2 and then Ctrl + V.

L42 · Accepted Answer · 2017-06-23 05:21:58Z

0

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

answered Jun 23, 2017 at 5:21

L42

19.8k11 gold badges47 silver badges70 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related