Run-time error: 1004 Range of object '_Workshe

Question

Okay here is my code, I'm pretty sure the error is coming from something silly in the way stuff is named. I'm just starting to learn VBA so totally noob at this and can't catch what's wrong. Any input would be appreciated.

Sub test()

Dim wsInput As Worksheet: Set wsInput = ActiveSheet
Dim wsOutput As Worksheet: Set wsOutput = Workbooks.Open("C:\output.xls").Sheets(1)
Dim OutputRowCount As Integer: OutputRowCount = 1

    For i = 1 To 10000
     If wsInput.Range("a12" & i) <> "" Then
         wsInput.Range("D12" & i, "E12" & i).Copy

       wsOutput.Range("A4" & OutputRowCount).PasteSpecial Paste:=xlPasteValues
    End If
    Next

End Sub

Answer 1

There's multiple errors/problems in your code:

1. Your statement wsInput.Range("a12" & i) certainly does not what you want - it'll return cells A121, A122, ..., A1210000! Instead, try wsInput.Range("A" & (12+i)) or wsInput.Range("A12").Offset(i-1). Same problem with the other ranges.

2. in wsInput.Range("D12" & i, "E12" & i).Copy you actually copy two cells (D12:E12, after fixing #1)- not sure you want this. If you want this, you could alternatively use the Resize method: wsInput.Range(D12).Offset(i-1).Resize(,2)

3. You do not increase OutputRowCount, therefore every cell will be pasted to A4 (after fix from #1, else to A41)! Add a line OutputRowCount=OutputRowCount+1.

4. Instead of copying and pasting, you could simply assign the .Value: wsOutputRange("A"& 4 + OutputRowCount).Resize(,2).Value = Input.Range(D12).Offset(i-1).Resize(,2).Value`

Last but not least, instead of looping over each cell, consider using .SpecialCells and Intersect, i.e. you could your whole For loop with

Application.Union( _
    wsInput.Range("A4").Resize(10000).SpecialCells(xlCellTypeFormulas),
    wsInput.Range("A4").Resize(10000).SpecialCells(xlCellTypeValues)) _
    .Offset(,3).Resize(,2).Copy
wsOutput.Range("A4").PasteSpecial(xlPasteValues)

Hope that helps!

Answer 2

The maximum amount of rows you can have in Excel 32-bit is 1048576, but the last row you are trying to access here is 1210000. The below code works (all I have done is changed 10000 to 9999), but as Peter says, this probably isn't what you really want to do, unless you have some bizarre business reason or something:

Sub test()

Dim wsInput As Worksheet: Set wsInput = ActiveSheet
Dim wsOutput As Worksheet: Set wsOutput = Workbooks.Open("C:\output.xls").Sheets(1)
Dim OutputRowCount As Integer: OutputRowCount = 1

    For i = 1 To 9999
     If wsInput.Range("a12" & i) <> "" Then
         wsInput.Range("D12" & i, "E12" & i).Copy

       wsOutput.Range("A4" & OutputRowCount).PasteSpecial Paste:=xlPasteValues
    End If
    Next

End Sub

Answer 3

Error: Method 'Paste' of object '_Worksheet' failed - 1004

Solution: Need to remeber the problems in Excel before copy the shapes from one sheet to another sheet. 1. Activate the Sheet(from where you are copying). 2. Select the Shapes from Sheet. 3. Copy the shapes from the Sheet. 4. Paste to shape to target sheet

Example: Previously my code is like below:

       Sheet1.Shapes(0).Copy
       Targetsheet.Paste

I have modified the like below:

       Sheet1.Activite
       Sheet1.Shapes(0).Select
       Sheet1.Shapes(0).Copy
       Targetsheet.Paste

Now it is working fine.