Increasing range for each iteration

Question

I'm automating an Excel sheet for my work and I'm stuck in a problem.

I'm trying to copy a specific range (A3:D3) and paste it to the ending row of another workbook. I'm trying to use an if statement to filter ranges that have the number 0 in cell B3.

Please help. I'm a complete rookie and I'm just starting out. Sorry if there's a lot of questions.

I've tried to change the range to a cell (i, 2) but it only copies B3 and not the rest (A3:D3).

Edit: forgot to add the s in cells Edit2: I just need to copy four cells (A3:D3) and increment it on my next iteration so that the copied cell would be (A4:D4)

Sub CopyData()

Dim wsCopy As Worksheet, wsDest As Worksheet
Dim iCopyLastRow As Long, iDestLastRow As Long

Set wsCopy = Workbooks("file1.xlsx").Worksheets("trend")
Set wsDest = Workbooks("file2.xlsx").Worksheets("raw data")

iCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

    For i = 3 To iCopyLastRow
        If wsCopy.Cells(i, 2).Value = 0 Then

        Else
        wsCopy.range(Cell(i,2), Cell(i,4)).Copy
        'wsCopy.Cells(i, 2). Copy ##this copies just one cell

        iDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
        wsDest.range("A" & iDestLastRow).PasteSpecial xlPasteValues
        End If

    Next i

Error messages:

Run-time error '1004':

Method 'Range' of object '_Worksheet' failed

and the debug highlights wsCopy.range(Cell(i,2), Cell(i,4)).Copy, the statement after else

Possible duplicate of [VBA: Getting run-time 1004: Method 'Range' of object '\_Worksheet' failed when using cells](https://stackoverflow.com/questions/27802286/vba-getting-run-time-1004-method-range-of-object-worksheet-failed-when-us) — FunThomas, Oct 09 '19 at 06:30
Change: `wsCopy.range(Cell(i,2), Cell(i,4)).Copy` For: `wsCopy.range(wsCopy.Cells(i,2), wsCopy.Cells(i,4)).Copy` You missed a `s` in `Cells` plus you need to full quallify the ranges even there. — Damian, Oct 09 '19 at 06:31
Hey Damian thanks! I forgot to add the s in my post. With the correct syntax, I still get the same error. — zen_4551, Oct 09 '19 at 07:13

Louis · Accepted Answer · 2019-10-09T07:57:52.613

Try using this code:

Sub CopyData()
    Dim wsCopy As Worksheet, wsDest As Worksheet
    Dim iCopyLastRow As Long, iDestLastRow As Long

    Set wsCopy = Workbooks("file1.xlsx").Worksheets("trend")
    Set wsDest = Workbooks("file2.xlsx").Worksheets("raw data")

    iCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

    For i = 3 To iCopyLastRow
        If wsCopy.Cells(i, 1).Value <> 0 Then
            'A = 1, D = 4
            wsCopy.Range(wsCopy.Cells(i, 1), wsCopy.Cells(i, 4)).Copy

            iDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

            wsDest.Range("A" & iDestLastRow).PasteSpecial xlPasteValues
        End If
    Next i
End Sub

Just make sure that iCopyLastRow and iDestLastRow are the values that you expect.

I hope this helps.

Hi Louis thanks for the reply! I forgot to add the s in my post and with it it still gives me the same error. — zen_4551, Oct 09 '19 at 07:20

score 0 · Answer 2 · answered Oct 09 '19 at 07:44

Try below code, it's ready to use in a loop:

Sub CopyAndAppend()
    Dim destSheet As Worksheet, srcSheet As Worksheet, lastRow As Long
    Set destSheet = Worksheets("Sheet2")
    Set srcSheet = Worksheets("Sheet1")
    ' determine last row in Sheet2
    lastRow = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    i = 3
    ' copy range A3:D3 and paste it right after last row in Sheet2
    srcSheet.Range(srcSheet.Cells(i, 1), srcSheet.Cells(i, 4)).Copy destSheet.Cells(lastRow + 1, 1)
    ' increment row index
    i = i + 1
    ' do something else
End Sub

Thanks Michal! I'll try this out in a bit. The code seems neater! I'll let you know if it does work. But I'm trying to figure out why my code isn't working. Also maybe I just need to get away from this. I'll be back in a bit — zen_4551, Oct 09 '19 at 07:49

Increasing range for each iteration

2 Answers2