Range类Autofilter方法失败：按联系人拆分Excel主表遇异常

核心错误原因 #

你遇到的Autofilter method of Range Class Failed错误，主要由以下问题导致：

1. AutoFilter语法错误：Criteria1:="NOT EQUAL TO" & cell.Value是错误写法，VBA中不等于的正确表达式是<>，且Operator:=xlFilterValues不适用于单条件不等于的场景，会直接导致过滤逻辑失效。
2. 依赖ActiveSheet/Select：这种写法稳定性极差，一旦工作表焦点意外变化就会引发引用错误，这也是只生成第一个子表就中断的关键原因之一。
3. 未处理空过滤结果：如果某个联系人没有匹配的数据，执行SpecialCells(xlCellTypeVisible)会直接报错，打断整个循环。

分步排查步骤 #

• 验证命名范围：
  • 按Ctrl+G打开定位窗口，输入Master，确认范围包含表头行且是连续的有效数据区域，没有空行或无效单元格打断。
  • 同样定位Splitcode，确认范围里的每个单元格都是非空的联系人名称，且和主表第6列的内容完全一致（注意大小写、首尾空格这类细节）。
• 检查原表过滤状态：打开主表"List of Old Clients (A-Z)"，确保没有开启自动过滤（如果有，点击数据选项卡的"清除"按钮关闭）。

修正后的代码 #

Sub SplitandFilterSheet()
    Dim splitRange As Range
    Dim cell As Range
    Dim masterSheet As Worksheet
    Dim newSheet As Worksheet
    
    ' 直接引用主表，彻底避免Select操作
    Set masterSheet = ThisWorkbook.Sheets("List of Old Clients (A-Z)")
    ' 引用Splitcode工作表的命名范围
    Set splitRange = ThisWorkbook.Sheets("Splitcode").Range("Splitcode")
    
    ' 确保主表初始无过滤状态
    If masterSheet.AutoFilterMode Then masterSheet.AutoFilterMode = False
    
    For Each cell In splitRange
        ' 跳过空单元格，避免创建无效名称的工作表
        If cell.Value <> "" Then
            ' 复制主表到最后，用变量直接引用新表
            masterSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            newSheet.Name = cell.Value
            
            With newSheet.Range("Master")
                ' 正确设置过滤规则：第6列不等于当前联系人
                .AutoFilter Field:=6, Criteria1:="<>" & cell.Value
                On Error Resume Next ' 处理无匹配行的异常情况
                ' 跳过表头，删除所有可见行
                .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                On Error GoTo 0 ' 恢复默认错误处理机制
                ' 关闭过滤
                .AutoFilter
            End With
        End If
    Next cell
End Sub

代码修改说明 #

1. 移除Select/ActiveSheet：用变量masterSheet和newSheet直接引用工作表，彻底杜绝焦点变化导致的引用错误。
2. 修正AutoFilter语法：用VBA标准的<>运算符代替错误的"NOT EQUAL TO"，移除无效的Operator:=xlFilterValues参数。
3. 增加异常处理：删除行前添加On Error Resume Next，避免因无匹配行而报错中断循环。
4. 跳过空单元格：避免Splitcode范围里的空值生成无效名称的工作表。
5. 初始化过滤状态：提前关闭主表的过滤，确保复制的新表初始状态干净。

额外注意事项 #

• 如果联系人名称包含斜杠、问号这类特殊字符，无法直接作为工作表名称，可在代码中添加替换逻辑（比如把特殊字符换成下划线）。
• 运行前建议备份原文件，避免误操作导致数据丢失。

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