CODEWITHSUNDEEP
excelvba
zen_4551
zen_4551

Reputation: 33

Increasing range for each iteration

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

Upvotes: 3

Views: 143

Answers (2)

Louis
Louis

Reputation: 3632

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.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

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

Upvotes: 0

Related Questions