Workbook sheets to compare values in columns and not copy with code if present

[DThib]

Thanks so much to all for the help with a value to stop one workbook from copying same info again.

This workbook needs the opposite. I need to loop through after the conditions are met and if PR is found, skip and go to next.

Here is what I have:

Sub Workie1()


  Dim LastRow, SecondRow As Long
  Dim i As Long
  Dim j As Long, BatchP As Long
  Dim Sp As Variant, x As Variant
    
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
    SecondRow = Sheets("SPR").Cells(Rows.Count, "B").End(xlUp).Row
    
    i = 1 + LastRow
    j = 1 + SecondRow
   For i = 1 To LastRow 'Each i In Sheets("Query")
        If Sheets("Sheet1").Cells(i, 22) = "Smith" And Sheets("Sheet1").Cells(i, 25) <> "" Then
             'Sheets("SPR").Cells(j, 1) = Format(Now(), "DD-MMM-YYYY")
            Do While Sheets("Sheet1").Cells(i, 2).Value <> Sheets("SPR").Cells(j, 1).Value
               Sheets("SPR").Cells(j, 1) = Sheets("Sheet1").Cells(i, 2).Value
             If Sheets("Sheet1").Cells(i, 2).Value = Sheets("SPR").Cells(j, 1).Value Then GoTo Line9
             'End If 'SPR
            End With
             If Sheets("Sheet1").Cells(i, 8).Value = "" Then
              Sheets("SPR").Cells(j, 2) = Sheets("Sheet1").Cells(i, 9).Value
             Else
               Sheets("SPR").Cells(j, 2) = Sheets("Sheet1").Cells(i, 8).Value 'AIC
             End If
             Sheets("SPR").Cells(j, 3) = Sheets("Sheet1").Cells(i, 16).Value 'SW Ver
             Sheets("SPR").Cells(j, 4) = Sheets("Sheet1").Cells(i, 24).Value 'Date Assigned
             If Sheets("Sheet1").Cells(i, 5) = 0 Then
               Sheets("SPR").Cells(j, 8) = ""
             Else: Sheets("SPR").Cells(j, 8) = Sheets("Sheet1").Cells(i, 5)
             End If 'SalesForce
             Sheets("SPR").Cells(j, 5) = Sheets("Sheet1").Cells(i, 18).Value 'DateSPREntered


           If Sheets("Sheet1").Cells(i, 10) <> "" Then
            Sp = Split(Replace(Sheets("Sheet1").Cells(i, 10).Value, "(", ")"), ")")
            With Application
              x = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(x) Then Sheets("SPR").Cells(j, 15).Value = x
           ElseIf Sheets("Sheet1").Cells(i, 11) <> "" Then
            Sp = Split(Replace(Sheets("Sheet1").Cells(i, 11).Value, "(", ")"), ")")
            With Application
              x = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(x) Then Sheets("SPR").Cells(j, 15).Value = x
           End If
            'Batch # Left(Cells(I, 10), BatchP + 1)
             Sheets("SPR").Cells(j, 21) = Sheets("Sheet1").Cells(i, 39).Value 'Date Occurred
             Sheets("SPR").Cells(j, 25) = Sheets("Sheet1").Cells(i, 31).Value 'Symptom
             Sheets("SPR").Cells(j, 30) = Sheets("Sheet1").Cells(i, 40).Value 'Submitted by Name
             Sheets("SPR").Cells(j, 18) = Sheets("Sheet1").Cells(i, 48).Value 'Failed Component Serial Number
             Sheets("SPR").Cells(j, 17) = Sheets("Sheet1").Cells(i, 49).Value 'Component Lot #
             Sheets("SPR").Cells(j, 34) = Sheets("Sheet1").Cells(i, 23).Value 'Status
         j = j + 1
Line9:  End If


   Next i
     
End Sub

It is running through but not placing the missing data in the sheet.
Help!

 

[DThib]

Anybody?

 

[DThib]

Here is the code that only attaches the last code again which I do not want it to do. I want it to place only new SPRs in the sheet.

Sub Workie1()


  Dim LastRow, SecondRow As Long
  Dim i As Long
  Dim j As Long, BatchP As Long
  Dim Sp As Variant, x As Variant
    
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
    SecondRow = Sheets("SPR").Cells(Rows.Count, "B").End(xlUp).Row
    
    i = 1 + LastRow
    j = 1 + SecondRow
   For i = 1 To LastRow                                                            
        If Sheets("Sheet1").Cells(i, 22) = "Smith, John" And Sheets("Sheet1").Cells(i, 25) <> "" Then
             'Sheets("SPR").Cells(j, 1) = Format(Now(), "DD-MMM-YYYY")
           While Not Sheets("SPR").Cells(j, 1) = Sheets("Sheet1").Cells(i, 2)
               Sheets("SPR").Cells(j, 1) = Sheets("Sheet1").Cells(i, 2).Value
             If Sheets("Sheet1").Cells(i, 2).Value = Sheets("SPR").Cells(j, 1).Value Then GoTo Line9
             'End If 'SPR
            'End With
             If Sheets("Sheet1").Cells(i, 8).Value = "" Then
              Sheets("SPR").Cells(j, 2) = Sheets("Sheet1").Cells(i, 9).Value
             Else
               Sheets("SPR").Cells(j, 2) = Sheets("Sheet1").Cells(i, 8).Value 'AIC
             End If
             Sheets("SPR").Cells(j, 3) = Sheets("Sheet1").Cells(i, 16).Value 'SW Ver
             Sheets("SPR").Cells(j, 4) = Sheets("Sheet1").Cells(i, 24).Value 'Date Assigned
             If Sheets("Sheet1").Cells(i, 5) = 0 Then
               Sheets("SPR").Cells(j, 8) = ""
             Else: Sheets("SPR").Cells(j, 8) = Sheets("Sheet1").Cells(i, 5)
             End If 'SalesForce
             Sheets("SPR").Cells(j, 5) = Sheets("Sheet1").Cells(i, 18).Value 'DateSPREntered
           Wend
           If Sheets("Sheet1").Cells(i, 10) <> "" Then
            Sp = Split(Replace(Sheets("Sheet1").Cells(i, 10).Value, "(", ")"), ")")
            With Application
              x = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(x) Then Sheets("SPR").Cells(j, 15).Value = x
           ElseIf Sheets("MK_DB1").Cells(i, 11) <> "" Then
            Sp = Split(Replace(Sheets("Sheet1").Cells(i, 11).Value, "(", ")"), ")")
            With Application
              x = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(x) Then Sheets("SPR").Cells(j, 15).Value = x
           End If
            'Batch # Left(Cells(I, 10), BatchP + 1)
             Sheets("SPR").Cells(j, 21) = Sheets("Sheet1").Cells(i, 39).Value 'Date Occurred
             Sheets("SPR").Cells(j, 25) = Sheets("Sheet1").Cells(i, 31).Value 'Symptom
             Sheets("SPR").Cells(j, 30) = Sheets("Sheet1").Cells(i, 40).Value 'Submitted by Name
             Sheets("SPR").Cells(j, 18) = Sheets("Sheet1").Cells(i, 48).Value 'Failed Component Serial Number
             Sheets("SPR").Cells(j, 17) = Sheets("Sheet1").Cells(i, 49).Value 'Component Lot #
             Sheets("SPR").Cells(j, 34) = Sheets("Sheet1").Cells(i, 23).Value 'Status
         j = j + 1
        
        End If
   Next i
Line9: ' End If
     
End Sub