I keep getting a 1004 Error Application-Defined or Object-Defined Error when trying to copy and paste

Question

I'm trying to copy Range A:3 to A:x (where x is the number of rows in the table "AgentName") from sheet "AgentName" However, whenever I try to copy the range with a variable in it, it gives me the Error 1004. I've read through everything I could find. Here's two common ones I've tried.

Worksheets("AgentName").Range("A3:A"&x).copy

Worksheets("AgentName").Range(Sheets("AgentName").Cells(3, 1), Sheets("AgentName").Cells(x, 1)).Copy

^I've tried it without the Sheets, with just cells, with cells and range together.

Here's a shortened version of the code:

Private Sub CommandButton1_Click()

    'CountRows in AgentName Table
    Dim myWorkSheet As Worksheet, myTable As ListObject, x As Long
    Set myWorkSheet = ActiveWorkbook.Worksheets("AgentName")
    Set myTable = myWorkSheet.ListObjects("AgentName")
    x = myTable.DataBodyRange.Rows.Count

    'Specific Forms for All Agents (Motivated)
    If Range("B3").Value = "All Agents" And Range("C3").Value = "Motivated" Then
        Worksheets("Other Forms").Range("A1:H2").Copy
        Worksheets("Master").Range("B6:I7").PasteSpecial
        Worksheets("AgentName").Range(Sheets("AgentName").Cells(3, 1), Sheets("AgentName").Cells(x, 1)).Copy
        Worksheets("Master").Range("B8:B13").PasteSpecial
        Range("B6:I13").Borders.LineStyle = xlContinuous

    End If
End Sub

Lucas Raphael Pianegonda Lucas Raphael Pianegonda · Accepted Answer · 2017-06-27T06:37:50

I see two potential sources of error: first of all you copy and paste a range with non constant size into a range with constant size namely here:

Worksheets("AgentName").Range(Sheets("AgentName").Cells(3, 1), Sheets("AgentName").Cells(x, 1)).Copy
    Worksheets("Master").Range("B8:B13").PasteSpecial

You paste A3:Ax into B8:B13 if x is not equal to 8 this will give you an error so just paste it into the first cell and excel will assume that you will want to paste the rest just below. So :

    Worksheets("Master").Range("B8").PasteSpecial

instead.

The second point is your construction of copying:

Worksheets("AgentName").Range(Sheets("AgentName").Cells(3, 1), Sheets("AgentName").Cells(x, 1)).Copy

I would rather work with the listobject if you have one already defined.

mytable.databodyrange(1,mytable.listrows.count).copy

This or something very similar should work. Be aware that .row gives you the absolute row and listrow will give you the row relative to the table. So mytable.databodyrange(1,3) will be the entry in the first row and the third column of the table no matter were the table is. This may be absolute row 9053 and absolute column 456. It is usually better to work with the relative coordinates like this you will be able to use code again for other purposes and you just need to adjust the table.

Good Luck I hope this was of help!

I keep getting a 1004 Error Application-Defined or Object-Defined Error when trying to copy and paste

1 Answers