Update this code to ask for column letter instead of number

[Poppyrob]

I have been updating this code to use on one of my projects. I have everything working as I wish, except for one thing. It will ask for which column I want to use, and it only accepts a numerical value in the box. I would like to have it accept the column letter instead. I will have several columns of data and dont want to have to count the columns each time.

The code itself splits a worksheet into multiple workbooks based on the column I select.

Option Explicit

Sub ExportToWorkbooks()
    
    Const aibPrompt As String = "Which column would you like to filter by?"
    Const aibtitle As String = "Filter Column"
    Const aibDefault As Long = 1
    
    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 ' folder not found
    If Left(dFileExtension, 1) <> "." Then dFileExtension = "." & dFileExtension
    
    Application.ScreenUpdating = False
    
    Dim sCol As Variant
    
    sCol = Application.InputBox(aibPrompt, aibtitle, aibDefault, , , , , 1)
    If Len(CStr(sCol)) = 0 Then Exit Sub ' no entry
    If sCol = False Then Exit Sub ' canceled
    
    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 ' not enough rows
    Dim srrg As Range: Set srrg = srg.Rows(1) ' to copy column widths
    Dim scrg As Range: Set scrg = srg.Columns(sCol)
    Dim scData As Variant: scData = scrg.Value
    
    ' Write the unique values from the 1st column to a dictionary.
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' case insensitive
    
    Dim Key As Variant
    Dim r As Long
    
    For r = 2 To srCount
        Key = scData(r, 1)
        If Not IsError(Key) Then ' exclude error values
            If Len(Key) > 0 Then ' exclude blanks
                dict(Key) = Empty
            End If
        End If
    Next r
    If dict.Count = 0 Then Exit Sub ' only error values and blanks
    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
        ' Add a new (destination) workbook and reference the first cell.
        Set dwb = Workbooks.Add(xlWBATWorksheet) ' one worksheet
        Set dws = dwb.Worksheets(1)
        Set dfcell = dws.Range("A1")
        ' Copy/Paste
        srrg.Copy
        dfcell.PasteSpecial xlPasteColumnWidths
        srg.AutoFilter sCol, Key
        srg.SpecialCells(xlCellTypeVisible).Copy dfcell
        sws.ShowAllData
        dfcell.Select
        ' Save/Close
        dFilePath = dFolderPath & Key & DateText & dFileExtension ' build the file path
        Application.DisplayAlerts = False ' overwrite without confirmation
        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

 

[Micron]

change aibDefault, , , , , 1) to aibDefault) or if preferred, aibDefault, , , , , 2)
and alter the code as required to work with Column(sCol) using text. I think you'd need a way to get the .Address property of something such as the ActiveCell or Target. However, I think I'd try type 8 for input box because that will return a range object. Then you might not need to try to relate text "F" to column property, which is number.

 

[Poppyrob]

Not sure I follow. I am not very good at VBA .. novice. Like most I found this code online, then started working with it to get to work with my situation. When try type 8 I get an error on the next line, mismatch.

 

[johnnyL]

You can easily convert a Column number to a column letter:

'Convert To Column Letter
ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)


In your case:
ColumnLetter = Split(Cells(1, sCol).Address, "$")(1)