0

Copying active row of Sheet1 to Sheet2 based on cell condition (Column F="Yes") and also prevent duplication. I tried the following 

Private Sub CommandButton1_Click()
Dim CustomerName As String, Customeraddress As String, Customercity As String, Custtel As String, Custzip As String
Worksheets("sheet1").Select
CustomerName = Range("A2")
Customeraddress = Range("B2")
Customercity = Range("C2")
Custtel = Range("D2")
Custzip = Range("E2")
Worksheets("sheet2").Select
Worksheets("Sheet2").Range("B4").Select
If Worksheets("Sheet2").Range("B4").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Customeraddress
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Customercity
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Custtel
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Custzip
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("C4").Select
End Sub

So now I need to know how to check if sheet1 Column F="Yes" for that particluar Customer. Then only copy from Sheet1 to Sheet2. If customer info is already present in Sheet2 then dont duplicate if the user clicks the button on sheet1 active row.

Community
  • 1
  • 1
user3331363
  • 305
  • 1
  • 4
  • 13
  • 1
    1) as I understood, user selects any row, if cell in column `F` in _active row_ is `"Yes"` than copy to sheet2, am I right? 2) how do you indicate (using what columns) that customer info already presented in sheet2 (is duplicate)? – Dmitry Pavliv Mar 07 '14 at 18:01
  • @Simoco 1)Yes, that's correct. 2)The first column A "Customer name" in Sheet1 and Column B "Customer name" in Sheet2. If the row highlighted in Sheet1 has customer name already present in Column B of Sheet2 then do not copy. – user3331363 Mar 07 '14 at 18:11

1 Answers1

1

Try this code:

Private Sub CommandButton1_Click()
    Dim lastrow As Long
    'if value in column F not equal "YES" - do nothing and exit sub
    If UCase(Range("F" & ActiveCell.Row).Value) <> "YES" Then Exit Sub

    With ThisWorkbook.Worksheets("Sheet2")
        lastrow = Application.Max(4, .Cells(.Rows.Count, "B").End(xlUp).Row + 1)

        'if CustomerName is already in column B of sheet2 - do nothing and exit sub
        If WorksheetFunction.CountIf(.Range("B1:B" & lastrow), _
            Range("A" & ActiveCell.Row).Value) > 0 Then Exit Sub

        .Range("B" & lastrow).Resize(, 5).Value = _
            Range("A" & ActiveCell.Row).Resize(, 5).Value
    End With
End Sub

And, please, read this post: How to avoid using Select/Active statements:)

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • 1
    this works really well!! Thanks a lot! The post on using Select/Active statements is very impressive. Thanks a lot again for sharing. I'm learning new crazy stuff everyday!! :) – user3331363 Mar 07 '14 at 18:28
  • have a question for you. Instead of doing this on a button is it possible to do this on the Yes\No drop down on Cell F. If user selects Yes then copy, but if user selects "Yes" & then "No" delete the copied row. For duplicates is it possible to give user prompt that row already exist and user can duplicate if clicked "Yes" & "No" wont duplicate. Please let me know. I'm learning with all our wonderful help. Thanks a lot! :) – user3331363 Mar 14 '14 at 16:49
  • 1
    Yes, it's possible, you should look into [Worksheet_Change event](http://msdn.microsoft.com/en-us/library/office/ff839775(v=office.15).aspx) I suggest you to post new question because it's far from original one and answer on it in comments would be really hard:) – Dmitry Pavliv Mar 14 '14 at 17:18
  • will post a new one. Thanks a lot!! – user3331363 Mar 14 '14 at 17:42
  • I posted my question here :) http://stackoverflow.com/questions/22412442/copy-excel-row-in-different-worksheet-when-cell-dropdown-yes-and-when-no-rem – user3331363 Mar 14 '14 at 20:53