• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to resolve run time error 1004 while using vba to paste data from 1 sheet to another


New Member
Hi everyone,

As described in the title i am using this code (PFB) to paste data from a table in a sheet to a table in another sheet but get this error " run-time error 1004 paste special method of range class failed".

Disclaimer: I have not written code before and I am totally new to this process.

Thank you for looking into this :)

>>> use code - tags <<<
Private Sub CommandButton1_Click()

    Dim WS As Worksheet
    Dim LR As Long
    Dim WS1 As Worksheet
    Dim LR1 As Long
    Set WS1 = Worksheets("Heme Tracker Output")
    Set WS = Worksheets("Heme Tracker Input")

    LR = WS.ListObjects("Query3").Range.Rows.Count
    LR1 = WS1.ListObjects("Table7").Range.Rows.Count

    WS1.Range(WS1.Range("A2"), WS1.Range("A" & LR1)).Clear
    WS.Range(WS.Range("A2"), WS.Range("A" & LR)).Copy
    WS1.Range("A2").PasteSpecial xlPasteValues ( line where error occurs )
    WS1.Range(WS1.Range("B2"), WS1.Range("B" & LR1)).Clear
    WS.Range(WS.Range("B2"), WS.Range("B" & LR)).Copy
    WS1.Range("B2").PasteSpecial xlPasteValues
Last edited by a moderator:

Marc L

Excel Ninja
PFB ?‼​
Hi, according to your guessing challenge try to do the same manually in order to see if the same error occurs …​


Excel Ninja
Staff member

Is it possible for you to attach a sample file with data and worksheets as per your application?