bikergranma
08-10-2018, 03:21 PM
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
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