如何根据行所属类别将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
相关产品推荐
相关产品推荐

