Macro not finding next available blank line

[bikergranma]

Here is the code I have been using. Once I changed the source workbook the information is copied starting at row 1 again and copies over anything that is there.


Sub CopyJobs()


Dim i As Long, j As Long, lstRow As Long
Dim wkbkSource As Workbook
Dim wsSource As Worksheet
Dim wkbkTarget As Workbook
Dim wsTarget As Worksheet
Dim sJobNum As String


sJobNum = "180112"


Set wkbkSource = Application.Workbooks("Trucking Jul-Dec 2018.xlsm")
Set wkbkTarget = Workbooks.Open("S:\VT Trucking, LLC\Driver Billing\Trucking 2018\Tracking by Job.xlsm")
Set wsTarget = Sheets("Job 180112")


j = 3


For Each wsSource In wkbkSource.Worksheets
With wsSource

lstRow = .Cells(.Rows.Count, 8).End(xlUp).Row

For i = 1 To lstRow
If .Cells(i, 8).Value = sJobNum Then
.Range("A" & i & ":U" & i).Copy wsTarget.Rows(j)
j = j + 1
End If
Next i
End With
Next


End Sub

[p45cal]

instead of j = 3
try:
j = wsTarget.Cells(wsTarget.Rows.Count, 8).End(xlUp).Row+1

[bikergranma]

[Solved] You are the BEST!!! Thank you. Works perfectly. :clap:

[bikergranma]

Is there a way I can get it to not post duplicates?

[p45cal]

Probably easiest to use Remove Duplicates (in the Data Tools section of the Data tab of the ribbon) after the routine has been run. It can be coded for.
You could get the code to check existing entries, but with some 20 columns to check and who knows how many rows, it would probably be easier to Remove Duplicates.
Unless you can say only 3 or 4 columns need to be compared to determine duplicates?

[bikergranma]

I had thought of that, but was wondering if there might be something else out there. I have 30+ jobs that will have information added to their perspective tabs. Running the macro once a month would work except the information is needed more often.
Thanks very much for your help!!:bow: