如何修改VBA拆分文件宏：将按列号筛选改为按列名筛选

没问题！我来帮你把这个宏改成按列名来筛选拆分数据，替换原来输入列号的逻辑。下面是修改后的完整代码，之后我会把关键改动点给你讲清楚，方便你理解：

Sub Split_Files_By_ColumnName()
    Const aibPrompt As String = "Which column name would you like to filter by?"
    Const aibtitle As String = "Filter Column Name"
    Const aibDefault As String = "Region" ' 默认列名可按需修改
    Dim dFileExtension As String: dFileExtension = ".xlsx"
    Dim dFileFormat As XlFileFormat: dFileFormat = xlOpenXMLWorkbook
    Dim dFolderPath As String: dFolderPath = "XYZ"
    If Right(dFolderPath, 1) <> "\" Then dFolderPath = dFolderPath & "\" 
    If Len(Dir(dFolderPath, vbDirectory)) = 0 Then Exit Sub ' 目标文件夹不存在则退出
    If Left(dFileExtension, 1) <> "." Then dFileExtension = "." & dFileExtension
    Application.ScreenUpdating = False
    
    ' 弹出输入框接收列名输入
    Dim colName As Variant
    colName = Application.InputBox(aibPrompt, aibtitle, aibDefault, , , , , 2) ' 类型2代表文本输入
    If Len(CStr(colName)) = 0 Then Exit Sub ' 无输入则退出
    If colName = False Then Exit Sub ' 用户取消则退出
    
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Sheet1")
    If sws.FilterMode Then sws.ShowAllData
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    Dim srCount As Long: srCount = srg.Rows.Count
    If srCount < 3 Then Exit Sub ' 数据行太少则退出
    
    ' 根据输入的列名查找对应列号
    Dim sCol As Long
    On Error Resume Next ' 捕获列名不存在的错误
    sCol = Application.Match(colName, srg.Rows(1), 0) ' 在表头行匹配列名
    On Error GoTo 0 ' 恢复默认错误处理
    
    ' 检查列名是否存在
    If sCol = 0 Then
        MsgBox "列名 """ & colName & """ 不存在，请重新输入！", vbExclamation
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    Dim srrg As Range: Set srrg = srg.Rows(1) ' 用于复制列宽
    Dim scrg As Range: Set scrg = srg.Columns(sCol)
    Dim scData As Variant: scData = scrg.Value
    
    ' 提取筛选列的唯一值存入字典（原逻辑保留）
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' 不区分大小写
    Dim Key As Variant
    Dim r As Long
    For r = 2 To srCount
        Key = scData(r, 1)
        If Not IsError(Key) Then ' 排除错误值
            If Len(Key) > 0 Then ' 排除空值
                dict(Key) = Empty
            End If
        End If
    Next r
    If dict.Count = 0 Then Exit Sub ' 仅错误值和空值则退出
    Erase scData
    
    ' 生成每个唯一值的拆分文件（原逻辑保留）
    Dim dwb As Workbook
    Dim dws As Worksheet
    Dim dfcell As Range
    Dim dFilePath As String
    For Each Key In dict.Keys
        Set dwb = Workbooks.Add(xlWBATWorksheet) ' 新建单工作表工作簿
        Set dws = dwb.Worksheets(1)
        Set dfcell = dws.Range("A1")
        
        ' 复制表头列宽和筛选后的数据
        srrg.Copy
        dfcell.PasteSpecial xlPasteColumnWidths
        srg.AutoFilter sCol, Key
        srg.SpecialCells(xlCellTypeVisible).Copy dfcell
        sws.ShowAllData
        dfcell.Select
        
        ' 保存并关闭文件
        dFilePath = dFolderPath & "Access Rights Review " & Key & dFileExtension
        Application.DisplayAlerts = False ' 覆盖文件时不提示
        dwb.SaveAs dFilePath, xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        dwb.Close SaveChanges:=False
    Next Key
    
    sws.AutoFilterMode = False
    Application.ScreenUpdating = True
    MsgBox "数据已成功导出！", vbInformation
End Sub

关键改动说明 #

• 输入框类型调整：把原来的Application.InputBox最后一个参数从1（数字输入）改成2（文本输入），适配列名的输入需求。
• 列名转列号逻辑：用Application.Match函数在表头行查找输入的列名，自动转换成对应的列号，完美适配列名变动的场景。
• 新增错误处理：如果用户输入的列名不存在，宏会弹出警告并退出，避免后续代码因无效列号报错。
• 默认值优化：把输入框的默认值从数字1改成了常见的列名"Region"，你可以根据自己的常用场景修改这个默认值。

内容的提问来源于stack exchange，提问作者Gonçalo Malveiro