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

Asked Sep 04 '20 at 12:53

Active Sep 04 '20 at 16:23

Viewed 331 times

i am trying to create a macro in excel to copy a certain data range and paste it at the end row of the table(after expanding it). The table is located in another workbook. Here is the code I am trying to execute -

Sub Macro9()

    Dim mySel As Range
    Dim ws As Worksheet
    Dim loTable As ListObject
    Dim LastRow As Long

    ActiveSheet.Select
    Set mySel = Selection.EntireRow
    mySel.Copy
    Workbooks.Open Filename:="/Users/achintyamittal/Desktop/list.xlsx"
    Set ws = Sheets("Sheet1")
    
    'Define Table Object
    Set loTable = ws.ListObjects("Truck_List")
    loTable.ListRows.Add
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    LastRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
  
    Range("A" & LastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues

End Sub

I am getting the error in the last line(PasteSpecial). I wanted to know how to solve to this error.

edited Sep 04 '20 at 16:23

asked Sep 04 '20 at 12:53

Achintya Mittal

Check the value of `LastRow` before pasting? Is it a number? – Foxfire And Burns And Burns Sep 04 '20 at 12:57
Try to avoid `ActiveSheet` & `Selection`. The code if you replace `LastRow` with a number works? – Error 1004 Sep 04 '20 at 13:00
No need to use the clipboard here... use [value transfer](https://stackoverflow.com/questions/51528000/vba-paste-as-values-how-to). – BigBen Sep 04 '20 at 13:03
@BigBen wow value transfer method works wonders. Cheers. – Achintya Mittal Sep 04 '20 at 19:45
Value transfer is a game-changer, yes. Faster and more reliable than using the clipboard. Happy to help. – BigBen Sep 04 '20 at 19:46

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

0 Answers0