Run-time error: 1004 Range of object '_Worksheet' failed

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

Could this help? http://stackoverflow.com/questions/12174723/run-time-error-1004-method-range-of-object-global-failed — D.Ginzbourg, Jun 04 '13 at 21:02

Peter Albert · Answer 1 · 2013-06-04T21:22:04.763

There's multiple errors/problems in your code:

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.
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)
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.
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!

Great! I'm going to give this a try as soon I can. Basically what I'm trying to do is something like this: I have a spreadsheet where a user inputs data starting with cell A12, data in cell A12 is converted to output in cells D12 and E12. I'm trying to make a button macro that will check to see if data exists in cell A12 and so forth until cell A44 and will copy the corresponding data in columns D and E without copying the blank fields. This will then get pasted into the output spreadsheet. Thanks again! — user2437807, Jun 05 '13 at 12:33
That's what the above code will do - you should only replace the 10000 with 32 to speed up execution... — Peter Albert, Jun 05 '13 at 19:54

score 1 · Answer 2 · answered Jun 04 '13 at 21:14

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

score 0 · Answer 3 · answered Sep 11 '13 at 06:30

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.

Run-time error: 1004 Range of object '_Worksheet' failed

3 Answers3

Linked