在單一工作簿內的 2 個工作表之間移動行

在單一工作簿內的 2 個工作表之間移動行

我在 1 個工作簿(單一 excel 檔案)中有 2 個工作表(讓它們命名為 worksheet1 和 worksheet2)。 workseet1 包含 2 個欄位:描述和列(2 個選擇:已完成和進行中)。

描述地位已完成/進行中

我的要求:當我選擇狀態=進行中(從下拉清單中)時,必須將整行移至第二個工作表,即工作表2。這樣我就只剩下工作表中狀態=完成的行。也就是說,工作表 1 將只包含狀態 = 已完成的行,第二個工作表(即工作表 2)將只包含狀態 = 進行中的行。

當我從狀態中選擇任何選項時,它就會移動到各自的工作表中。

TIA

答案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

相關內容