vb excel error 1004 Selection.PasteSpecial

Question

I have a macro in Excel but it shows me error 1004 when I try to paste using Selection.PasteSpecial.

This error is with Office 365 if I try the same macro with Office 2010 it works ok.

The debugger shows an error in this line:

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The full code of the module is:

Sub MoveData_Activations()

MoveData_Activations Macro

    Dim dayCount As Integer
    Dim startCell As String
    Dim curCellRef As String

    dayCount = 13
    startCell = "B3"

    If MsgBox("Are you sure you want to rollover the data for a new date?", vbYesNo, "Confirm rollover") = vbYes Then
        ActiveSheet.Protect UserInterfaceOnly:=True
        Range(startCell).Select
        ActiveCell.Cells(-1, 6).Copy
        ActiveCell.Cells(-1, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        curCellRef = Range(startCell).Address
        For i = 1 To dayCount
            CopyData curCellRef, False, 6, 24, 2
            curCellRef = ActiveCell.Cells(1, 6).Address
        Next i
        CopyData curCellRef, True, 6, 24, 2
    End If
End Sub

Not sure, but read [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) anyway. — Kyle, Dec 06 '16 at 17:35

score 0 · Answer 1 · edited May 23 '17 at 12:08

It is always time consuming copying contents in the clipboard, it may be that either is used by another program, hence the error or memory somehow is messed up. For short, change these lines:
From:

 Range(startCell).Select
        ActiveCell.Cells(-1, 6).Copy
        ActiveCell.Cells(-1, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

To:

Range(startCell).Offset(-1, 6).Copy destination:=Range(startCell).Offset(-1, 1)

Hint: Avoid selection

vb excel error 1004 Selection.PasteSpecial

1 Answers1