VBA Code to copy and paste to last row in another sheet

Question

Sub CopyRowsWithNUMBER()

Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet


Set Source = ActiveWorkbook.Worksheets("Completed Items")
Set Target = ActiveWorkbook.Worksheets("Week 24")

j = 15
For Each c In Source.Range("D1:D200")
    If c = "24" Then
       Source.Rows(c.Row).Copy Target.Rows(j)
       j = j + 1
    End If
    
   
Next c
End Sub

This is my Code I need to know how to set this to paste my code to the first available row in the target sheet. Right now it is set to move it to row 15, I did this as a temporary workaround since that is in fact the current last row of my target data sheet but of course this will change.

Solution

Adapt the following lines to find first available row:

    Dim lastRow As Long
    'Finds the last occupied cell on Column A. Change 1 to another number. B=2, D=4, E=5, etc
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Set value to +1 to get the next available row
    lastRow = lastRow + 1

Comments

[Damidge]

Thanks for your help, much appreciated. I added your suggestions, now its copying and pasting the data starting at row 86? Dim lastRow As Long lastRow = Cells(Rows.Count, 2).End(xlUp).Row For Each c In Source.Range("D1:D200") If c = "24" Then Source.Rows(c.Row).Copy Target.Rows(lastRow) lastRow = lastRow + 1 End If Next c End Sub

[Reddy Lutonadio]

@Damidge If and only if this answer your question, you can mark it as answered.