Finding last row VBA

[owen4512]

Hi all,

I'm currently having an issue with the below vba - The below works perfectly up until the below line;

            Cells(last_row_Active, 2).PasteSpecial xlPasteValuesAndNumberFormats

In want the vba to loop through data in sheet2 and copy rows with the text "Outstanding" and paste the result onto sheet5. The issue i'm having is that the results found from sheet2 are copied and pasted onto sheet5 but in the same row (So it's overwriting the previous result). If i then run the macro again, the above line finds the next empty row but still pastes all results in the same row once again. Here's the full code;

Sub Create_Report()

    Dim i, last_row_Data, last_row_Active As Integer
    Dim Records As Long

    Records = Sheet1.Range("N15").Value
    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    last_row_Active = Application.WorksheetFunction.CountA(Sheet5.Range("B:B")) + 8 'Finds last row on 'Report' tab
    
    For i = 2 To last_row_Data
        If Sheet2.Range("M" & i).Text = "Outstanding" Then
            Records = Records + 1
            Sheet2.Activate
            rng = "B" & i & ", C" & i & ", E" & i & ", H" & i & ", I" & i & ", N" & i
            Sheet2.Range(rng).Copy
            Sheet5.Activate
            Cells(last_row_Active, 2).PasteSpecial xlPasteValuesAndNumberFormats
        End If
     Next i
    
End Sub

 

[offthelip]

change this line:

Cells(last_row_Active, 2).PasteSpecial xlPasteValuesAndNumberFormats

to

Cells(last_row_Active, 2).PasteSpecial xlPasteValuesAndNumberFormats
last_row_Active=last_row_Active+1

 

[owen4512]

change this line:

Cells(last_row_Active, 2).PasteSpecial xlPasteValuesAndNumberFormats

to

Cells(last_row_Active, 2).PasteSpecial xlPasteValuesAndNumberFormats
last_row_Active=last_row_Active+1

Lovely! - Works perfect, thanks