修改Excel拆分工作表VBA代码：支持输入列字母替代列号

原代码仅支持输入列序号作为拆分依据，以下是修改后的代码，可支持输入列字母（如A、AB、XYZ等）：

Option Explicit

Sub ExportToWorkbooks()

    Const aibPrompt As String = "Which column would you like to filter by? (Enter column letter(s), e.g. A, AB)"
    Const aibTitle As String = "Filter Column"
    Const aibDefault As String = "C" ' 改为默认列字母
    
    Dim dFileExtension As String: dFileExtension = ".xlsx"
    Dim dFileFormat As XlFileFormat: dFileFormat = xlOpenXMLWorkbook
    Dim dFolderPath As String: dFolderPath = "C:\Users\WalteR01\Desktop\VPN Revalidations\Split by Manager\"
    
    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 sColInput As Variant
    ' 修改输入框类型为2（文本），允许输入列字母
    sColInput = Application.InputBox(aibPrompt, aibTitle, aibDefault, , , , , 2)
    If Len(CStr(sColInput)) = 0 Then Exit Sub ' 未输入内容则退出
    If sColInput = False Then Exit Sub ' 取消操作则退出
    
    Dim sCol As Long
    ' 将输入的列字母转换为列序号，同时验证输入有效性
    On Error Resume Next
    sCol = Range(sColInput & "1").Column
    On Error GoTo 0
    If sCol = 0 Then ' 输入无效列字母
        MsgBox "Invalid column letter entered!", vbExclamation
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    Dim sws As Worksheet: Set sws = ActiveSheet
    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 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
    Dim DateText As String: DateText = Format(Date, "_mm_yyyy")
    
    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 & Key & DateText & dFileExtension ' 构建文件路径
        Application.DisplayAlerts = False ' 覆盖时不弹出确认提示
        dwb.SaveAs dFilePath, xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        dwb.Close SaveChanges:=False
    Next Key
    
    sws.AutoFilterMode = False
    Application.ScreenUpdating = True
    
    MsgBox "Data exported.", vbInformation

End Sub

关键修改说明 #

• 调整输入框类型：将Application.InputBox的类型参数从1（仅允许数字）改为2（允许文本输入），支持列字母输入
• 更新默认值：把默认筛选列从数字3改为列字母"C"，匹配新的输入方式
• 新增列字母转序号逻辑：通过Range(sColInput & "1").Column将输入的列字母转换为列序号，同时添加错误验证，若输入无效列字母则弹出提示并退出
• 修改提示文本：更新输入框的提示内容，明确告知用户可输入列字母（如A、AB）

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