VBA formula that will insert a column in sheet2 and shows if the row is copied from sheet1.

[JaimeMabini]

Hello,

I have this VBA formula that compares the attributes in sheet1 to sheet2.

• If attributes in sheet1 is found in sheet2, copy the entire row and paste in sheet2.
  
  

  Sub UpdateSheet2()
  
  On Error Resume Next
  
    Dim i As Long
    Dim f As Range, c As Range
    Sheets("Sheet1").Select
    last = Range("B19").Value
    
    Application.ScreenUpdating = False
    With Sheets("Sheet2")
      For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
        Set f = Sheets("Sheet1").Range("A1", last).Find(c.Value, , xlValues, xlWhole, , , False)
        
        If Not f Is Nothing Then
          f.EntireRow.Copy
          .Range("A" & c.Row).PasteSpecial xlValues
          
        End If
      Next
    End With
    Application.CutCopyMode = False
    
  End Sub

  
  Code is perfectly working.
  
  Now what I need to do/add is insert a column in Sheet2 that will show "Yes" if the row is copied from sheet1 and "No" if not.
  
  Thank you. Any help will be highly appreaciated.

 

[DanteAmor]

Note: dont use this instruction: On Error Resume Next. If the macro has a problem, you won't know where in which problem.

In column Z the results will be Yes / No
Try this:

Sub UpdateSheet2()
  Dim i As Long
  Dim f As Range, c As Range
  Sheets("Sheet1").Select
  last = Range("B19").Value
  
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      Set f = Sheets("Sheet1").Range("A1", last).Find(c.Value, , xlValues, xlWhole, , , False)
      
      If Not f Is Nothing Then
        f.EntireRow.Copy
        .Range("A" & c.Row).PasteSpecial xlValues
        .Range("Z" & c.row).value = "Yes"
      Else
        .Range("Z" & c.row).value = "No"
      End If
    Next
  End With
  Application.CutCopyMode = False
  
End Sub

 

[JaimeMabini]

Note: dont use this instruction: On Error Resume Next. If the macro has a problem, you won't know where in which problem.

In column Z the results will be Yes / No
Try this:

Sub UpdateSheet2()
  Dim i As Long
  Dim f As Range, c As Range
  Sheets("Sheet1").Select
  last = Range("B19").Value
 
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      Set f = Sheets("Sheet1").Range("A1", last).Find(c.Value, , xlValues, xlWhole, , , False)
     
      If Not f Is Nothing Then
        f.EntireRow.Copy
        .Range("A" & c.Row).PasteSpecial xlValues
        .Range("Z" & c.row).value = "Yes"
      Else
        .Range("Z" & c.row).value = "No"
      End If
    Next
  End With
  Application.CutCopyMode = False
 
End Sub

Note: dont use this instruction: On Error Resume Next. - Noted.

And this code is perfectly working! You are Awesome!!! Thank you ?

 

[DanteAmor]

And this code is perfectly working! You are Awesome!!! Thank you ?

I'm glad to help you. Thanks for the feedback.