☰

在单个工作簿中的两个工作表之间移动行

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
在单个工作簿中的两个工作表之间移动行

我在 1 个工作簿（单个 Excel 文件）中有 2 个工作表（我们将它们命名为 worksheet1 和 worksheet2）。workseet1 包含 2 列：说明和列（2 个选择：已完成和进行中）。

描述和地位（已完成/进行中）

我的要求：当我选择状态 = 进行中（从下拉列表中）时，必须将整行移动到第二个工作表，即工作表 2。这样，工作表中就只剩下状态 = 已完成的行。也就是说，工作表 1 将仅包含状态 = 已完成的行，而第二个工作表，即工作表 2 将仅包含状态 = 进行中的行。

当我从状态中选择任何选项时，它就会移动到其各自的工作表。

短暂性脑缺血发作

答案1

不完全确定您为什么要分解数据，但您可以使用Worksheet_Change()VBA 中的事件来完成您想要的操作。

这在已完成的工作表代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    'This goes into your "Completed" worksheet's module

    Dim RngB As Range
    Set RngB = Intersect(Target, Range("B:B"))

    If RngB Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Dim cel As Range, wsInProgress As Worksheet, retVal As Variant

    '!!! Change the worksheet name to whatever it is that moves from your completed
    'worksheet to the in-progress worksheet...
    Dim wsInProgress As Worksheet
    Set wsInProgress = ThisWorkbook.Worksheets("In-Progress")

    For Each cel In RngB.Cells
        Debug.Print cel.Address
        If cel.Value = "In-Progress" Then
            wsInProgress.Rows(nextrow(wsInProgress)) = cel.EntireRow.Value
            cel.EntireRow.Delete
        End If
    Next
    Application.EnableEvents = True
End Sub

这在正在进行的工作表代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    'This goes into your "In-Progress" worksheet's module

    Dim RngB As Range
    Set RngB = Intersect(Target, Range("B:B"))

    If RngB Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Dim cel As Range, wsInProgress As Worksheet, retVal As Variant

    '!!! Change the worksheet name to whatever it is that moves from your completed
    'worksheet to the in-progress worksheet...
    Dim wsCompleted As Worksheet
    Set wsCompleted = ThisWorkbook.Worksheets("Completed")

    For Each cel In RngB.Cells
        Debug.Print cel.Address
        If cel.Value = "Completed" Then
            wsInProgress.Rows(nextrow(wsCompleted)) = cel.EntireRow.Value
            cel.EntireRow.Delete
        End If
    Next
    Application.EnableEvents = True
End Sub

这进入了一个标准模块：

Public Function nextRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
    nextRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row + 1
End Function

相关内容