请求论坛大神帮忙精简代码

吴永飞

请求论坛大神帮忙精简代码，只复制值，不要格式谢谢！

Private Sub Worksheet_Change(ByVal TargetAs Range)

   If Target.Column = 13 Then

    If InStr(Target, "离职") Then

Cells(Target.Row, 2).Copy Sheets("停保人员明细").Range("B" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row + 1)

Cells(Target.Row, 3).Copy Sheets("停保人员明细").Range("C" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 4).Copy Sheets("停保人员明细").Range("D" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 5).Copy Sheets("停保人员明细").Range("E" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 6).Copy Sheets("停保人员明细").Range("F" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 7).Copy Sheets("停保人员明细").Range("G" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 8).Copy Sheets("停保人员明细").Range("H" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 9).Copy Sheets("停保人员明细").Range("I" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Cells(Target.Row, 10).Copy Sheets("停保人员明细").Range("J" & Sheets("停保人员明细").Cells(Rows.Count, 5).End(xlUp).Row)

Sheets("停保人员明细").Range("L"& Sheets("停保人员明细").Cells(Rows.Count,5).End(xlUp).Row) = Date

Target.EntireRow.Delete

    End If

   End If

End Sub

chxw68

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     If Target.Column = 13 Then
   
3.         If InStr(Target, "离职") Then
   
4.             r1 = Target.Row
   
5.             With Worksheets("停保人员明细")
   
6.                 r2 = .Cells(.Rows.Count, 5).End(xlUp).Row + 1
   
7.                 .Cells(r2, 12) = Date
   
8.             End With
   
9.             For j = 2 To 10
   
10.                 Cells(r1, j).Copy Worksheets("停保人员明细").Cells(r2, j)
    
11.             Next
    
12.             Target.EntireRow.Delete
    
13.         End If
    
14.     End If
    
15. End Sub

复制代码

baofa2

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.   Dim ar, i As Long, j As Long
   
3.   If Target.Column = 13 Then
   
4.     If InStr(Target.Value, "离职") Then
   
5.       ar = Cells(Target.Row, 2).Resize(, 9).Value
   
6.       With Worksheets("停保人员明细")
   
7.         i = .Cells(Rows.Count, 5).End(xlUp).Row
   
8.         With .Range("B" & i)
   
9.           .Offset(1).Value = ar(1, 1)
   
10.           For j = 2 To UBound(ar, 2)
    
11.             .Offset(, j - 1) = ar(1, j)
    
12.           Next
    
13.           .Offset(, j) = Date
    
14.         End With
    
15.       End With
    
16.       Target.EntireRow.Delete
    
17.     End If
    
18.   End If
    
19. End Sub

复制代码

taller

吴永飞

非常感谢各位大佬