You need to enable JavaScript to run this app. 如何根据行所属类别将Excel工作表指定行移动到其他工作表
优惠活动
大模型
产品
解决方案
定价
更多

如何根据行所属类别将Excel工作表指定行移动到其他工作表

VBA 按行所属类别自动移动至对应工作表实现

你当前编写的代码可实现将master工作表中D列值为Done的行移动到completed工作表,代码如下:

Sub MoveBasedonValue() 

Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long 

A = Worksheets("master").UsedRange.Rows.Count
B = Worksheets("completed").UsedRange.Rows.Count 

If B = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("completed").UsedRange) = 0 Then B = 0
End If
Set xRg = Worksheets("master").Range("D1:D" & A)

On Error Resume Next

Application.ScreenUpdating = False

For C = 1 To xRg.Count
    If CStr(xRg(C).Value) = "Done" Then
        xRg(C).EntireRow.Copy Destination:=Worksheets("completed").Range("A" & B + 1)
        xRg(C).EntireRow.Delete
        If CStr(xRg(C).Value) = "Done" Then
            C = C - 1
        End If
        B = B + 1
    End If
Next
Application.ScreenUpdating = True

End Sub

如果要扩展为根据行的所属类别自动匹配对应目标工作表移动,可使用修改后的代码:

Sub MoveByCategory()
    Dim masterSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRowMaster As Long
    Dim lastRowTarget As Long
    Dim i As Long
    ' 配置类别与目标工作表的映射关系,可按需增减
    Dim categoryMap As Variant
    categoryMap = Array( _
        "Done", "completed", _
        "In Progress", "processing", _
        "To Do", "pending", _
        "Cancelled", "cancelled" _
    )
    
    Set masterSheet = ThisWorkbook.Worksheets("master")
    lastRowMaster = masterSheet.UsedRange.Rows.Count
    
    Application.ScreenUpdating = False
    ' 倒序遍历避免删除行后索引错乱
    For i = lastRowMaster To 1 Step -1
        Dim cellVal As String
        cellVal = CStr(masterSheet.Range("D" & i).Value)
        Dim mapIndex As Integer
        mapIndex = -1
        ' 匹配类别对应的目标表
        For j = LBound(categoryMap) To UBound(categoryMap) Step 2
            If categoryMap(j) = cellVal Then
                mapIndex = j
                Exit For
            End If
        Next j
        ' 匹配到对应类别则执行移动
        If mapIndex <> -1 Then
            Set targetSheet = ThisWorkbook.Worksheets(categoryMap(mapIndex + 1))
            lastRowTarget = targetSheet.UsedRange.Rows.Count
            If lastRowTarget = 1 And Application.WorksheetFunction.CountA(targetSheet.UsedRange) = 0 Then
                lastRowTarget = 0
            End If
            masterSheet.Rows(i).Copy Destination:=targetSheet.Range("A" & lastRowTarget + 1)
            masterSheet.Rows(i).Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

使用说明:

  • 代码中categoryMap数组可自行调整,按「类别值、对应目标工作表名」的顺序依次添加映射规则即可
  • 执行代码前建议先备份原文件,避免数据误操作丢失
  • 如果需要支持自动创建不存在的目标工作表,可在匹配到类别后增加工作表存在性判断逻辑,不存在时自动新建即可

内容的提问来源于stack exchange,提问作者isimi taiwo

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.13 14:00:01