Issue with a run-time error 1004: PasteSpecial method of Range Class failed

Question

I am trying to run a paste value macro in a different sheet in a workbook that previously had a password protection.

The Macro works fine in the destination sheet for the values but not in the other tabs where i get this error runtime error '1004' - select method of range class failed

How should i proceed to allow this macro to run from a different active sheet?

Sub PasteSpecial_ValuesOnly()

    Worksheets("ARF Table").Range("A2:AI13").Copy
'Error occurs below
    **Worksheets("ARF Export").Range("A2:AI13").Select**
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Worksheets("ARF Export").Range("AK2").Value = Worksheets("ARF Export").Range("AD2").Value

End Sub

Unknown Unknown · Accepted Answer · 2018-10-18T12:59:57

You only require the top left cell of the destination.

You don't need to .Select and direct value transfer is faster.

Sub PasteSpecial_ValuesOnly()

    Worksheets("ARF Table").Range("A2:AI13").Copy
    Worksheets("ARF Export").Range("A2").PasteSpecial Paste:=xlValues

    Worksheets("ARF Export").Range("AK2").Value = Worksheets("ARF Export").Range("AD2").Value

End Sub

Alternate,

Sub PasteSpecial_ValuesOnly()

    with Worksheets("ARF Table").Range("A2:AI13")
      Worksheets("ARF Export").Range("A2").resize(.rows.count, .columns.count) = .value
    end with

    Worksheets("ARF Export").Range("AK2").Value = Worksheets("ARF Export").Range("AD2").Value

End Sub

Issue with a run-time error 1004: PasteSpecial method of Range Class failed

1 Answers