根据单元格条件将工作表1的活动行复制到工作表2并避免重复

Question

基于单元格的条件（列f =" yes"）将Sheep1的活动行复制到Sheet2，也可以防止重复。我尝试了以下

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

因此，现在我需要知道如何检查Sheet1列f ="是"是否为该特定客户客户。然后仅复制从Sheet1到Sheet2。如果Enger2中已经存在客户信息，则如果用户单击Sheet1 Active行上的按钮，则不要复制。

Answer 1

尝试以下代码：

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

，请阅读此帖子：如何避免使用Select/Active语句：）