Appreciate any suggestion

Question

If the source and destination dates match and I use the following formula, I can copy and paste certain cells from a worksheet called "Jobs" with a raw data table to a worksheet called "Schedule." But it only copies one of the three jobs I have scheduled for that day. I would appreciate any assistance.

Sub CopyBasedonSheet1()

  Dim i As Long
  Dim j As Long
  
  Worksheets("Schedule").Range("B1:AJ92").ClearContents
  
  Sheet1LastRow = Worksheets("Jobs").Range("G" & Rows.Count).End(xlUp).Row 'G is the Date Column'
  Sheet2LastRow = Worksheets("Schedule").Range("A" & Rows.Count).End(xlUp).Row 'A is the Date column'
  
  For j = 1 To Sheet1LastRow
    For i = 1 To Sheet2LastRow
            
      If Worksheets("Jobs").Cells(j, 7).Value = Worksheets("Schedule").Cells(i, 1).Value And Worksheets("Jobs").Cells(j, 1).Value = "P" Then
        Worksheets("Schedule").Cells(i, 2).Value = Worksheets("Jobs").Cells(j, 3).Value 
        Worksheets("Schedule").Cells(i, 3).Value = Worksheets("Jobs").Cells(j, 9).Value 
        Worksheets("Schedule").Cells(i, 4).Value = Worksheets("Jobs").Cells(j, 14).Value 
      End If
    Next i
  Next j

End Sub

Answer 1

You could try and add a feature to concatenate multiple jobs:

 If Not IsEmpty(Worksheets("Schedule").Cells(i, 4).Value) Then Worksheets("Schedule").Cells(i, 4).Value = Worksheets("Schedule").Cells(i, 4).Value & ", " & Worksheets("Jobs").Cells(j, 14).Value.

 Make sure to clear the data before starting, otherwise, it will duplicate the jobs and/or display old data..