Comparing rows in two sheets

[reader6886]

Hi . I have managed to put in a vba code for my task , learning from here and there. what it does is.
1.when I click on a cell on a sheet 1 it copies the whole cell row on sheet 2.
2. It renames sheet 2 dynamically with info ( copied row from sheet 1 and used row on sheet 2 ) which is very helpful.

what i am trying to do is add if then else statement so that.
1.when i click a cell on sheet 1 . it first checks if the active cell row value is already on sheet 2 or not.
a if it is it gives " already added" message .
b. if not it copies whole active cell row from sheet 1 to sheet 2 like usual

what would be working the expression to carry this out. what do i need to put in the bold line .below

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    
    Dim sh As Worksheet, shnext As Worksheet
    
 
    Set sh = ActiveSheet
    Set shnext = sh.Next




[B]
If ActiveCell.EntireRow.Value matches with row on shnext  Then[/B]
MsgBox(" already there")

Else

ActiveCell.EntireRow.Copy _

shnext.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End If



   Dim LR As Long
 

  LR = shnext.Cells(Rows.Count, 1).End(xlUp).Row

   shnext.Name = ActiveCell.Row & "=" & LR


Application.CutCopyMode = False



End Sub

Other "feature" I wanted to add is if I wanted to delete the row on sheet 2 after it has been added i just need to click it. I did manage to make the following code work by putting it in sheet 2 . But it deletes the row from sheet 1 too . I want the delete happening in sheet 2 only not sheet 1 . What am i doing wrong . What do i need to change

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)



ActiveCell.EntireRow.Delete _


End Sub

 

[Saurabhj]

Hi,

Created below based on some assumptions. Please check.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim sh As Worksheet, shnext As Worksheet
    Dim lRow As Integer, found As String, rowno As Integer
    
    Set sh = ActiveSheet
    Set shnext = sh.Next
    
    lRow = shnext.Cells(Rows.Count, 1).End(xlUp).Row
    found = "n"
    
    For rowno = 1 To lRow
        If ActiveCell.Value = shnext.Cells(rowno, ActiveCell.Column) Then
            MsgBox ("Already there")
            found = "y"
            Exit For
        End If
    Next
    
    If found = "n" Then
        ActiveCell.EntireRow.Copy shnext.Cells(lRow + 1, 1)
    End If

    Dim LR As Long
     
    LR = shnext.Cells(Rows.Count, 1).End(xlUp).Row
    shnext.Name = ActiveCell.Row & "=" & LR
    Application.CutCopyMode = False
End Sub

 

[reader6886]

Hi,

Created below based on some assumptions. Please check.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim sh As Worksheet, shnext As Worksheet
    Dim lRow As Integer, found As String, rowno As Integer
   
    Set sh = ActiveSheet
    Set shnext = sh.Next
   
    lRow = shnext.Cells(Rows.Count, 1).End(xlUp).Row
    found = "n"
   
    For rowno = 1 To lRow
        If ActiveCell.Value = shnext.Cells(rowno, ActiveCell.Column) Then
            MsgBox ("Already there")
            found = "y"
            Exit For
        End If
    Next
   
    If found = "n" Then
        ActiveCell.EntireRow.Copy shnext.Cells(lRow + 1, 1)
    End If

    Dim LR As Long
    
    LR = shnext.Cells(Rows.Count, 1).End(xlUp).Row
    shnext.Name = ActiveCell.Row & "=" & LR
    Application.CutCopyMode = False
End Sub

THANK YOU SO SO MUCH . works great . happy holidays and happy new year .

 

[Saurabhj]

Great. Thanks for the feedback.

Happy new year.