VBA：多工作表匹配列标题应用筛选时出现命名参数错误

错误原因拆解 #

咱们一步步梳理你代码里的问题，导致Named argument not found报错和逻辑偏差的核心原因有这几个：

• 筛选逻辑完全颠倒：你写的是「当列标题不在filterCols数组里时执行筛选」，这和你想要的「仅在存在匹配列标题的工作表中应用筛选」完全相反，应该是找到匹配Status的列时才触发筛选。
• AutoFilter参数搭配错误：Operator:=xlFilterValues这个参数是用来筛选多个值（比如传入数组Array("INACTIVE", "TERMINATED")）的，而你这里只筛选单个值"INACTIVE"，用这个操作符会导致参数不匹配，直接触发命名参数找不到的错误。
• 固定列号不合理：你硬写Field:=1，但Status列不一定在第一列，应该先找到该列的实际位置，再指定Field参数。
• 不必要的列遍历：第二个循环里倒着遍历所有列，其实你只需要定位Status这一列，不需要遍历所有列做无效判断。

修正后的完整代码 #

Sub ManipulateSheets()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim a As Long, w As Long
    Dim keepCols As Variant
    Dim filterCols As Variant
    Dim wkbk1 As Workbook
    Dim statusCol As Long
    
    Set wkbk1 = Workbooks("testWorkbook.xlsm")
    
    '指定要操作的工作表
    Set ws2 = wkbk1.Sheets("mySheet")
    Set ws3 = wkbk1.Sheets("hisSheet")
    Set ws4 = wkbk1.Sheets("herSheet")
    
    keepCols = Array("Employee Number", "Status")
    filterCols = Array("Status")
    
    wkbk1.Activate
    '在指定工作表插入表头行
    ws2.Range("A1").EntireRow.Insert
    ws2.Range("A1").Value = "Employee Number"
    ws3.Range("A1").EntireRow.Insert
    ws3.Range("A1").Value = "Employee Number"
    ws4.Range("A1").EntireRow.Insert
    ws4.Range("A1").Value = "Employee Number"
    
    '统一所有工作表的表头名称
    For Each ws1 In wkbk1.Sheets
        With ws1.Cells(1, 1).EntireRow
            .Replace What:="USERID", Replacement:="Employee Number", Lookat:=xlWhole
            .Replace What:="STATUS", Replacement:="Status", Lookat:=xlWhole
            .Replace What:="USER_ID", Replacement:="Employee Number", Lookat:=xlWhole
            .Replace What:="USER-ID", Replacement:="Employee Number", Lookat:=xlWhole
            .Replace What:="USER_STATUS", Replacement:="Status", Lookat:=xlWhole
            .Replace What:="HR_STATUS", Replacement:="Status", Lookat:=xlWhole
        End With
    Next ws1
    
    With wkbk1
        '删除不需要的列
        For w = 1 To .Worksheets.Count
            With .Worksheets(w)
                For a = .Columns.Count To 1 Step -1
                    If UBound(Filter(keepCols, .Cells(1, a), True, vbTextCompare)) < 0 Then
                        .Columns(a).EntireColumn.Delete
                    End If
                Next a
            End With
        Next w
        
        '仅在包含Status列的工作表应用筛选
        For w = 1 To .Worksheets.Count
            With .Worksheets(w)
                statusCol = 0
                '查找Status列的位置，加错误处理避免找不到列崩溃
                On Error Resume Next
                statusCol = .Rows(1).Find(What:="Status", LookIn:=xlValues, LookAt:=xlWhole).Column
                On Error GoTo 0
                
                If statusCol > 0 Then
                    '先清除已有筛选，避免冲突
                    If .AutoFilterMode Then .AutoFilterMode = False
                    '对找到的Status列应用筛选
                    .UsedRange.AutoFilter Field:=statusCol, Criteria1:="INACTIVE"
                End If
            End With
        Next w
    End With
End Sub

关键修改点说明 #

• 修正筛选逻辑：先在每个工作表的表头行精准定位Status列，找到后才执行筛选，去掉了无意义的全列遍历。
• 修复AutoFilter参数：移除错误的Operator:=xlFilterValues，单个值筛选直接用Criteria1:="INACTIVE"即可；同时用找到的实际列号statusCol代替固定的Field:=1。
• 增加错误防护：用On Error Resume Next和On Error GoTo 0处理找不到Status列的情况，避免代码崩溃。
• 优化代码结构：把表头替换操作封装在With块里简化代码；增加清除已有筛选的步骤，避免多次筛选导致的逻辑冲突。

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