输入交货日期，然后将行复制到另一个工作表 [英] Enter delivery date then copy row to another worksheet

问题描述

Hi,

我在一个工作表上有一个表格，其中在与给定设备相对应的每一行上输入信息。在表格的第J列中，我们手动输入"x"表。一旦该设备交付。我试图用x作为两件事的触发器

I have a table on one worksheet where info is entered on each row corresponding to a given device. In column J of the table, we manually enter an "x" once that device is delivered. I am trying to use that x as a trigger for 2 things

1。在下一栏（J）中输入交货日期

1. To enter the delivery date in the next column (J)

2。将数据输入工作表中的整行复制到单独的工作表（Eval_List_P）

2. To copy the entire row from the data entry worksheet onto a separate worksheet (Eval_List_P)

步骤2似乎使用以下代码自行运行：

Step 2 seems to be working well on it's own using the following code:

Private Sub Worksheet_Change（ByVal Target as Range ）

'在交付时添加到评估列表



Dim C作为范围

如果相交（目标，Me.Range（"J5：J500"））则什么都没有然后退出Sub



每个C在相交中（目标，Me.Range（" J5：J500"）） .Cells

如果C.Text =" x"然后



C.EntireRow.Copy工作表（"Eval_List_P"）。细胞（行。计数，"B"）。结束（xlUp）。偏移（1）.EntireRow





结束如果

Next



End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'add to eval list when delivered

Dim C As Range
If Intersect(Target, Me.Range("J5:J500")) Is Nothing Then Exit Sub

For Each C In Intersect(Target, Me.Range("J5:J500")).Cells
If C.Text = "x" Then

C.EntireRow.Copy Worksheets("Eval_List_P").Cells(Rows.Count, "B").End(xlUp).Offset(1).EntireRow


End If
Next

End Sub

但我无法弄清楚如何在复制行之前先添加日期。

But I can't figure out how to add the date first before the row is copied.

另外，有没有办法我可以自动从Eval_List表中删除一行，如果"x"，从主表上的第I列删除。目前，如果设备被标记为错误交付，那么我们必须手动从
中删除Eval列表以及取消选中交付列。

Additionally, is there a way I can automatically remove a row from the Eval_List sheet if the "x" from column I on the master sheet is deleted. Currently, if a device is marked as delivered in error then we have to manually remove from the Eval list as well as unchecking the delivery column.

非常感谢任何帮助！

推荐答案

试试这个：

Private Sub Worksheet_Change(ByVal Target As Range)
    'add to eval list when delivered
    Dim C As Range
    If Intersect(Target, Me.Range("J5:J500")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each C In Intersect(Target, Me.Range("J5:J500")).Cells
        If C.Text = "x" Then
            C.Offset(0, 1).Value = Date
            C.EntireRow.Copy Worksheets("Eval_List_P") _
                .Cells(Rows.Count, "B").End(xlUp).Offset(1).EntireRow
        End If
    Next C
    Application.EnableEvents = True
End Sub

要从Eval_List_P中删除一行，我们需要拥有该行的唯一标识符。

To delete a row from Eval_List_P, we would need to have a unique identifier of the row.

这篇关于输入交货日期，然后将行复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！