VBA字典循环时按键对应选择工作表并粘贴过滤数据

原代码错误分析 #

• 工作表选择逻辑错误：Sheets(dict.Items).Select中dict.Items返回的是字典所有值的集合，并非当前循环对应Key的单个工作表名，导致每次循环选中所有目标工作表，最终所有粘贴操作都指向最后激活的工作表，且最后一次循环的数据会覆盖之前的内容。
• 依赖激活工作表：频繁使用Select/Selection会让代码逻辑依赖当前激活的工作表，极易出现逻辑混乱，且运行效率低下。
• 复制范围不合理：直接复制整列Columns("H:O")会包含过滤后的隐藏行，不符合仅粘贴过滤数据的需求。

修正后的代码 #

Sub FilterAndPasteToSheets()
    Dim dict As Object
    Dim key As Variant
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim rngFilter As Range
    Dim rngCopy As Range
    
    ' 初始化字典，绑定键与目标工作表
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add 200, "GSF"
    dict.Add 400, "REN"
    dict.Add 500, "P&T"
    dict.Add 600, "RIG"
    dict.Add 800, "O&G"
    
    ' 绑定源数据所在工作表
    Set wsSource = ThisWorkbook.Worksheets("Calculate")
    ' 绑定需要过滤的数据区域
    Set rngFilter = wsSource.Range("$H$8:$O$100")
    
    ' 关闭屏幕更新，提升运行效率
    Application.ScreenUpdating = False
    
    ' 遍历字典中的每个键
    For Each key In dict.Keys
        ' 清除源表之前的筛选状态
        wsSource.AutoFilterMode = False
        
        ' 按当前键筛选指定列（Field为过滤区域内的列索引，与原代码逻辑保持一致）
        rngFilter.AutoFilter Field:=7, Criteria1:=key
        
        ' 获取过滤后的可见单元格区域，防止无匹配数据时报错
        On Error Resume Next
        Set rngCopy = rngFilter.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        ' 存在可见数据时执行粘贴操作
        If Not rngCopy Is Nothing Then
            ' 绑定当前键对应的目标工作表
            Set wsTarget = ThisWorkbook.Worksheets(dict(key))
            
            ' 清空目标表原有数据（若需追加数据，可改为查找最后空行再粘贴）
            wsTarget.Cells.Clear
            
            ' 直接复制可见区域到目标表A1起始位置
            rngCopy.Copy Destination:=wsTarget.Range("A1")
            
            Set rngCopy = Nothing
        End If
    Next key
    
    ' 清除源表的筛选状态
    wsSource.AutoFilterMode = False
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
    
    MsgBox "数据分表粘贴完成！"
End Sub

代码说明 #

1. 对象化操作：通过定义工作表变量直接操作，彻底摆脱对Select的依赖，逻辑更稳定清晰。
2. 精准复制可见数据：使用SpecialCells(xlCellTypeVisible)仅复制过滤后的有效数据，避免包含隐藏行。
3. 错误防护：增加错误处理逻辑，防止无匹配数据时代码中断。
4. 性能优化：关闭屏幕更新减少闪烁，提升代码运行速度。

内容的提问来源于stack exchange，提问作者Mate7